Alan Alan - 16 days ago 10
MySQL Question

Clarification of MySQL index required to be "spanning" of all "and groups"

From the MySQL documentation here


Any index that does not span all AND levels in the WHERE clause is not used to optimize the query. In other words, to be able to use an index, a prefix of the index must be used in every AND group.


What exactly does this mean? Does it mean that for an index to be used, that every component of the AND query must refer to that index?

So lets say we have a
Person
table with
SID (primary)
,
first_name (index)
,
last_name
.

Does that mean that for the following query

Select * from Person where first_name='foo' and last_name='bar'


will not use the index on
first_name
?

Answer

An AND group is a set of comparisons that are combined with AND. A WHERE clause has multiple AND groups if it uses OR to combine several of these, e.g.

WHERE (col1 = 1 AND col2 = 2 AND col6 = 10) OR (col1 = '5' AND col4 = 'B' AND col2 = 16)

has two AND groups. There's one group that tests col1, col2, and col6, and another group that tests col1, col4, and col2.

So an index can be used if it has a prefix that's tested in every one of these groups. For instance, an index on (col1, col2, col3) could be used because the prefix (col1, col2) spans both groups.