[therealdrag0]

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.

[JoshuaDavid]

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.

[branko_d]

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

[mrits]

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