Index on Multiple Dimensions

Question

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?

Answer 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.