5 ms·

> 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 so

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

etiennebch 6y ago
Ah thanks for the clarification !
therealdrag0 6y ago
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.
- mrits 6y ago
  I think you just need to realize that an index on <companyId, userId> is a single index
- branko_d 6y ago
  > 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.
- JoshuaDavid 6y ago
  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.
tarasmatsyk 6y ago
Ha-ha, man, that is a gem comment I am getting your book
mhotchen 6y ago
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.
rsecora 6y ago
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 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. :)

etiennebch 6y ago

Ah thanks for the clarification !

therealdrag0 6y ago

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.

mrits 6y ago
I think you just need to realize that an index on <companyId, userId> is a single index
branko_d 6y ago
> 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.
JoshuaDavid 6y ago
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.

tarasmatsyk 6y ago

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

mhotchen 6y ago

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.

rsecora 6y ago

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 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. :)