From: Joerg Bruehe
Date: October 3 2010 3:44pm
Subject: Re: Indexing question
List-Archive: http://lists.mysql.com/mysql/223224
Message-Id: <4CA8A4EC.5000308@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable

Hi Neil, all!


Tompkins Neil wrote:
> So if you have individual indexes for example field_1, field_2 and fiel=
d_3
> etc and then perform a search like
>=20
> WHERE field_1 =3D 10
> AND field_3 =3D 'abc'
>=20
> This wouldn't improve the search ?  You have to create a index for all
> possible combined field searches ?

No - you didn't read Gavin's mail exact enough:

> On Fri, Oct 1, 2010 at 9:35 PM, Gavin Towey <gtowey@stripped> wrote:
>=20
>> [[...]]
>>
>> Additionally indexes are always read left to right.  So an index on
>> ('user_id', 'product_id') will help when doing WHERE user_id=3DN AND
>> product_id IN (1,2,3), but wouldn't help for just the condtion on
>> product_id.

What Gavin calls "left to right" is what I call "most significant
first", the result is the same:

In a multi-column index, the columns are listed in the order of their
significance. Any DBMS (this is not limited to MySQL) can use such an
index only if a condition for the first (=3D most significant) field(s) i=
s
(are) specified.

Example: Assume the index is on fields A, B, and C in that order.

A statement "... where A =3D x and B =3D y and C =3D z" can use the index=
=2E
A statement "... where A =3D x and B =3D y" can use the index, limited to=

the first two fields.
A statement "... where A =3D x" can use the index. the first field only.
A statement "... where A =3D x and C =3D z" can also use the index for A,=

but will have to evaluate the condition on C by scanning all records
matching A.

A statement "... where B =3D y and C =3D z" cannot use the index, because=

there is no condition on A.

If there are many searches based on A and C only (not B), and there are
many records matching A with different values of C, then an additional
index on these two columns may be helpful.

Compare the index with a phone book, which (typically) lists the entries
sorted by last name (most significant), then first name, then ... :
If you don't know the last name, you cannot profit from the sorting and
have to scan the wole book.

>>
>> See the manual for full details on how mysql uses indexes:
>> http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

HTH,
J=F6rg

--=20
Joerg Bruehe,  MySQL Build Team,  joerg.bruehe@stripped
ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603