about mysql index, multi-column or one?

Question

I have a sql like this:

SELECT * FROM tableA WHERE column_a = sth AND colun_b > sth

so, how do I create index for it?

• One index for two column
• Two indexes for each column

which is better?

Answer 1

If you are optimizing for exactly that one query, then a will be the best optimization. Your WHERE clause will check column_a first, and the proceed to the next row if that isn't a match. It will only proceed to check column_b if it is a match at column_a. Therefore, you want an index that is sorted by column_a first, and then by column_b, rather than two separate indexes.

Consider this as opposed to an OR clause, where you would generally have wanted two separate indexes, because OR says, "first select all the matches for column_a and then select all the matches for column_b, independently, and finally merge the result sets together". Since that query doesn't use feedback from column_a when it checks column_b, you want to use distinct indexes there.

Also note that this is all written in very general terms. These things may change from case to case depending on what your data looks like. One good way is to create one index, see how performance is, drop it, create the other, and choose the best one.

Answer 2

To have better reading you should have one index on two columns if your query is including both the comparisons.

However, you may create another index on second column if your query has only second column in where clause.

Answer 3

The thing to remeber is that an MySql index for 2 or more column in the same index work in order that you have made your index.

So to be optimized you have to put your column_a first in the index and then colun_b. This is to avoid a table scan.

Answer 4

You want an index which can be RANGE SCANNED. Use EXPLAIN (see the doc), it is your friend.

An index on column_a,column_b should be able to be range scanned in this case, but always check EXPLAIN. Use EXPLAIN on a non-production database with the same schema and volume/distribution of data as your production system (will be)