Alan Alan - 1 year ago 70
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
table with
SID (primary)
first_name (index)

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download