MySQL Forums
Forum List  »  Performance

Re: Multiple indexes - how do they work?
Posted by: Øystein Grøvlen
Date: August 29, 2016 01:23AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
592
August 25, 2016 03:51AM
Re: Multiple indexes - how do they work?
359
August 29, 2016 01:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.