ANDed search has the form
, as in this
WHERE column1 = 'x' AND column2 = 'y'
Here, the optimizer's decision process can be described as follows:
If (neither condition is indexed) use sequential scan.
Otherwise, if (one condition has better join type) then pick a driver based on join type (see Section 220.127.116.11, “Determining the Join Type”).
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_mergeindex 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
s2 = 5 as the driver because the
s2 was created first. Regard this
as an accidental effect rather than a rule — it could
change at any moment.