An AND
ed search has the form
, as in this
example:
condition1
AND
condition2
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 7.2.2.1, “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_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.