CODEWITHSUNDEEP
sqlindexing
FFMG
FFMG

Reputation: 1257

How to index query with 'OR' in the query?

if I have a query 

SELECT * FROM tableA where A=10 and (B=20 OR C=30) order by D

Normally I would create an index on the column, (A,B,C,D) but I am not sure that this is really the best way.

Would 2 indexes (A,B,D) and (A,C,D) no be better for this query?

Thanks

FFMG

Upvotes: 1

Views: 216

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415820

This query will force a full table or index scan. There is no way to index for a B or C scenario that will allow you to do an index seek.

What you can do is build an (A, D, include B, C) index (if your db supports this, other which just build an A,D,B,C index). Then, instead of a table scan, perhaps you only need an index scan, where the index is already in the correct order. This is especially true if you can complete the query using only these columns or just one or two others, which this index might entirely fulfill the query without needing to go back to the table.

Upvotes: 1

Related Questions