that was eye-opening indeed. Before reading his blog, I had no clue how indexes worked and how to write SQL that match the index structure (for instance the order of conditions in the where clause matter if you want to leverage a multi-column index).

> for instance the order of conditions in the where clause matter if you want to leverage a multi-column index

Ups, if this is you take away, then I've done something wrong.

Let me correct that: The order of columns in an index matters, not the order of conditions in the where clause.

This is the best part of HN.

Not only you did a comment on previous post, but it's a comment from the author of an awesome guide [https://use-the-index-luke.com].

Markus, I can assure you, that your guide has saved a lot of machine-computed-hours over the world with less energy consumption/wasted. Great work. :)

Ah thanks for the clarification !

This book has easily been one of the most influential on my career. Having excellent SQL skills has been my secret weapon for a while now.

And IIRC this holds MongoDB and I'd assume other non-SQL DBs.

If you have an index on <companyId, userId>, and you query with a userID only, that index wont be used. But if the index was <userId, companyId> then that index would be used. Or if you supplied both userId and companyId in your query, then either index would work.

And yes, this occasionally means that adding a

    where id in (select id from company)

sometimes will switch your query from doing a full table scan to using an index, fixing your problem for long enough to prepare a fix to add the appropriate index. Not that I've ever had to do something like that or anything.

> If you have an index on <companyId, userId>, and you query with a userID only, that index wont be used.

Surprisingly, it could be used in some circumstances, just not for the regular seek.

If the index is small (compared to the base table), the DBMS may decide to perform a full index scan (instead of the full table scan), especially if your SELECT list doesn't contain columns which are not in the index.

And Oracle can employ so called "skip scan" if it realizes that the number of distinct companies is small. This is essentially a separate seek under each distinct company.

I think you just need to realize that an index on <companyId, userId> is a single index

Ha-ha, man, that is a gem comment I am getting your book

I have also enjoyed his blog. Here's another way to understand indexes by implementing them in a simple SQL database. This includes both storing/retrieving indexed values and also deciding whether or not a query has an applicable index.

https://notes.eatonphil.com/database-basics-indexes.html