0

I've recently started reading the book Designing Data-Intensive Applications.

This book has revealed that I have been using indexes incorrectly, in that I didn't realize that even if I have an index on two columns on a single table, my query can only use one of those indexes!

For the past 3 years, I've been using indexes as if they are composable, when they are very much not so! (This is a limitation of the B-Tree index - I won't go into the details).

One solution to this problem is to use a concatenated index, in which we take all the columns we want to search / filter by, combine the data into one piece of data, and then search on that combined piece of data.

This likely works great in practice, but again, the indexes are not composable. If I have a concatenated index (on my persons table) on the first name and last name column, and now I want to search / filter on age as well, I have to create a new index.

Are there any general solutions to this composability problem with indexes?

0
1

Indexes can be combined, an "index intersection". (I'm from SQL Server side, hence that terminology etc.)

For example, you search for lastname = Smith and firstname = Joe, and neither is by itself very selective. So, go and find the keys for lastname and go find the keys for firstname and then join those two sets of keys to find the data you are after.

3
  • Hello - thank you for the response. I think my question was more about combining indeces "on the fly". In other words, if I have an index on column a, and and index on column b, I can't compose both indeces and use them. I'd have to create a new key on column a and column b. I believe this is a limitation of what an index is - some kind of data structure (for argument's sake, a B-Tree) that is "indexed" by one key. – Steven L. Aug 27 '20 at 12:55
  • 1
    Ah, you mean using the two as if you had an index on (a,b) or (b,a)? No, to the best of my knowledge this isn't happening. At least not in SQL Server. I don't know if it is possible with a B-tree structure in general, but I doubt it is. – Tibor Karaszi Aug 28 '20 at 7:27
  • Tibor - yes that it exactly what I was asking. I also do not think it is possible. – Steven L. Aug 30 '20 at 16:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.