MySQL Internals Manual  /  ...  /  AND Relations 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, “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 Section, “Index Merge Optimizer”.

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.

Download this Manual
EPUB - 0.8Mb
User Comments
  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.
Sign Up Login You must be logged in to post a comment.