Re: Multiple indexes - how do they work?
Hi Rathi,
In general, MySQL will use only one index per query. However, in the special case with multiple equality conditions, MySQL may use multiple indexes. For your query, it may find entries with col1=xxx from idxcol1 and entries with col2=yyy from idxcol2 and select those rows for which col3 (primary key) is found in both indexes. This can easily be done because within each value of col1/col2, index will be sorted on col3. Since the two indexes provides all requested columns, the actual row will not be accessed.
Alternatively, it can use one index, find all rows that matches the corresponding condition, and then use the primary key (col3) to lookup the row. The evaluation of other condition(s) will then be based on the content of the row.
A third alternative is to scan the entire table. Which alternative is chosen will depend on the selectivity of the conditions. If both conditions are highly selective, index merge is a good candidate. If only one of the conditions are highly selective, using the corresponding index is probably the best choice. If both conditions match a large fraction of the rows, a full table scan will probably be cheaper. MySQL uses a cost-based optimizer to determine which alternative to use.
However, as Rick already has mentioned, a multicolumn index on (col1, col2) will be even better. Then you will only need to read those index entries that match both your conditions, and since col3 is also present in the index, the actual row will not have to be accessed.
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway