MySQL Internals Manual  /  ...  /  AND Relations

7.2.3.1 AND Relations

An ANDed search has the form condition1 AND condition2, as in this example:

WHERE column1 = 'x' AND column2 = 'y'

Here, the optimizer's decision process can be described as follows:

  1. If (neither condition is indexed) use sequential scan.

  2. Otherwise, if (one condition has better join type) then pick a driver based on join type (see Section 7.2.2.1, “Determining the Join Type”).

  3. Otherwise, since (both conditions are indexed and have equal join type) pick a driver based on the first index that was created.

    The optimizer can also choose to perform an index_merge index intersection, as described in Index Merge Optimization.

Here's an example:

CREATE TABLE Table1 (s1 INT, s2 INT);
CREATE INDEX Index1 ON Table1 (s2);
CREATE INDEX Index2 ON Table1 (s1);
...
SELECT * FROM Table1 WHERE s1 = 5 AND s2 = 5;

When choosing a strategy to solve this query, the optimizer picks s2 = 5 as the driver because the index for s2 was created first. Regard this as an accidental effect rather than a rule — it could change at any moment.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Rick James on August 21, 2015
For case 3, does the optimizer even know which index was built first? I contend that it uses the 'smaller' index.

Hint to user: create a 'composite' INDEX(column1, column2) (in either order). That will give you better performance than any other option discussed here.