From: Jonas Galvez Date: October 6 2010 9:33pm Subject: Re: Indexing question List-Archive: http://lists.mysql.com/mysql/223256 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=0016e6509b508a7be90491f986fc --0016e6509b508a7be90491f986fc Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Thanks Gavin and Joerg, that was very helpful! -- Jonas On Sun, Oct 3, 2010 at 12:44 PM, Joerg Bruehe wrot= e: > Hi Neil, all! > > > Tompkins Neil wrote: > > So if you have individual indexes for example field_1, field_2 and > 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 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 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(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= . > 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 > > -- > 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 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=3Djonasgalvez@stripped > > --0016e6509b508a7be90491f986fc--