Creating indexes for 'OR' operator in queries

Question

I have some MySQL queries with conditions like

where field1=val1 or field2=val2

and some like

where fieldx=valx and fieldy=valy and (field1=val1 or field2=val2)

How can I optimize these queries by creating indexes? My intuition is to create separate indexes for field1 and field2 for first query as it is an OR, so a composite index probably won't do much good.

For the second query I intend to create 2 indexes: fieldx, fieldy, field1 and fieldx,fieldy,field2 again for the above stated reason.

Is this solution correct? This is a really large table so I can't just experiment by applying indexes and explaining the query.

Answer 1

My intuition is to create separate indexes for field1 and field2 for first query as it is an OR, so a composite index probably won't do much good.

That's correct.

For the second query I intend to create 2 indexes: fieldx, fieldy, field1 and fieldx,fieldy,field2 again for the above stated reason.

That's one option, the other will be an index on fieldx, fieldy, field1 and an index on field2 (same as for you first query!). Now you also have 2 indexes, but the second one will be much smaller. Your second query can use both indexes, the bigger one for the AND-part of your query and the small index for the OR part of field2. MySQL should be smart enough nowadays.

EXPLAIN will help you out.

Answer 2

As with all DBMS optimisation questions, it depends on your execution engine.

I would start with the simplest scenario, four separate indexes on each of the columns.

This will ensure that any queries using those columns in a way you haven't anticipated will still run okay (a fieldx/fieldy/field1 index will be of zero use to a query only using fieldy).

Any decent execution engine will efficiently choose the index with lowest cardinality first so as to reduce the result set and then perform the other filters based on that.

Then, and only if you have a performance problem, you can look into improving it with different indexes. You should test performance on production-type data, not any test databases you have built yourself (unless they mirror the attributes of production anyway).

And keep in mind that database tuning is rarely a set-and-forget operation. You should periodically re-tune because performance depends both on the schema and the data you hold.

Even if the schema never changes, the data may vary wildly. Re your comment "I just cant experiment by applying indexes and explaining the query", that's exactly what you should be doing.

If you're worried about playing in production (and you should be), you should have another environment set up with similar specs, copy the production data across to it, then fiddle around with your indexes there.