Comment by pixeloution

I'm not sure I could give an in-depth answer, especially under pressure in an interview. What I could do is explain the important bits at a high level:

    * how a B-Tree index is structured (and how that affects range queries, and wildcard queries)
    * how a query uses a single index per table, or can create a merge index
    * how a covering index increases performance
    * how composite indexes affect queries, why you don't need an index on both (col-a) and (col-a, col-b)

I don't think its unreasonable to expect a senior developer to know this stuff. It affects your ability to write performant queries against larger (1M - 1B rows) datasets.