Speeding up queries with combined indexes
Does it speed up queries to use a combined index on the fields you're searching one, rather than indexes on each field?
eg if we were to query with the condition:
If we have an index on col1 and and index on col2, the optimiser would only use one of the indexes, would it not? however if we added
SELECT * FROM my_table WHERE col1 < 100 AND col2>300
The optimiser seems to prefer this joined index. I haven't run any benchmarks, but is this the way to go? Utilising a combined index for a combination of fields you are likely to use in your condition? Maybe there's a better term for it that I could look up.
alter my_table add index combined_index(col1,col2)