Menu
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.
1000
The IN clause becomes an equality condition for each of the list and will use an index if appropriate. In the case of unique IDs and a large enough table then I'd expect the optimiser to use an index.
Yes, MySQL can use multiple index for a single query. The optimizer will determine which indexes will benefit the query. You can use EXPLAIN to obtain information about how MySQL executes a statement.
If we want to create indexes for unique values in a column, we use the CREATE UNIQUE INDEX constraint. For example, -- create unique index CREATE UNIQUE INDEX college_index ON Colleges(college_code); Here, the SQL command creates a unique index named college_index on the Colleges table using the college_code column.
Up to 32 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view.
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.
72
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.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
