From: Neil Tompkins
Date: October 3 2010 5:15pm
Subject: Re: Indexing question
List-Archive: http://lists.mysql.com/mysql/223225
Message-Id: <03286E97-7791-4E4D-A0EB-B015CCBEFEF5@googlemail.com>
MIME-Version: 1.0 (iPod Mail 7E18)
Content-Type: text/plain;
	charset=utf-8;
	format=flowed;
	delsp=yes
Content-Transfer-Encoding: quoted-printable

Thanks for your reply. So should we create individual indexes on each =20=

field or a multiple column index ??



On 3 Oct 2010, at 16:44, Joerg Bruehe <joerg.bruehe@stripped> wrote:

> Hi Neil, all!
>
>
> Tompkins Neil wrote:
>> So if you have individual indexes for example field_1, field_2 and =20=

>> field_3
>> etc and then perform a search like
>>
>> WHERE field_1 =3D 10
>> AND field_3 =3D 'abc'
>>
>> This wouldn't improve the search ?  You have to create a index for =20=

>> 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:
>>
>>> [[...]]
>>>
>>> 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=20=

> (s) is
> (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.
> 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 =20=

> 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 =20
> 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 =20=

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