Mysql index optimization

Date: 2012.01.02 | Tags:

I’ll explain by example. Say you have a table with these three columns:

org_id
domain_id
user_id

Say you have an index based on those three columns with the particular order:

index: org_id, domain_id, user_id

If you were to run the following queries, your query would use an index. Note that you don’t have to use all three columns for the index to be used, but your query does have to reference the columns in the same order as your index.

where org_id = ? and domain_id = ? and user_id ? 
where org_id = ?

However, if you do the following query, you would not be so lucky, as it does not follow the order of your index.

where domain_id = ?

For this query, you would create an index for only domain_id. Also note, you don’t have to create an index when making queries with only org_id as that is already taken care of by the first index above.