From: Neil Tompkins Date: October 7 2011 6:55pm Subject: Re: MySQL Indexes List-Archive: http://lists.mysql.com/mysql/225963 Message-Id: <5E2E4760-D2DD-4027-9BBF-423D1A18A5C6@googlemail.com> MIME-Version: 1.0 (iPhone Mail 8L1) Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Can you give more information as to why the second index would be of no use ?= =20 On 7 Oct 2011, at 18:24, Michael Dykman wrote: > No, I don't think it can be called. It is a direct consequence of the > relational paradigm. Any implementation of an RDBMS has the same > characteristic. >=20 > - md >=20 > On Fri, Oct 7, 2011 at 12:20 PM, Reindl Harald wro= te: >=20 >> but could this not be called a bug? >>=20 >> Am 07.10.2011 18:08, schrieb Michael Dykman: >>> When a query selects on field_a and field_b, that index can be used. If= >>> querying on field_a alone, the index again is useful. Query on field_b >>> alone however, that first index is of no use to you. >>>=20 >>> On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps wrote:= >>>=20 >>>> This thread has sparked my interest. What is the difference between an >>>> index on (field_a, field_b) and an index on (field_b, field_a)? >>>>=20 >>>>=20 >>>> On 10/06/2011 07:43 PM, Nuno Tavares wrote: >>>>=20 >>>>> Neil, whenever you see multiple fields you'd like to index, you should= >>>>> consider, at least: >>>>>=20 >>>>> * The frequency of each query; >>>>> * The occurrences of the same field in multiple queries; >>>>> * The cardinality of each field; >>>>>=20 >>>>> There is a tool "Index Analyzer" that may give you some hints, and I >>>>> think it's maatkit that has a tool to run a "query log" to find good >>>>> candidates - I've seen it somewhere, I believe.... >>>>>=20 >>>>> Just remember that idx_a(field_a,field_b) is not the same, and is not >>>>> considered for use, the same way as idx_b(field_b,field_a). >>>>>=20 >>>>> -NT >>>>>=20 >>>>>=20 >>>>> Em 07-10-2011 00:22, Michael Dykman escreveu: >>>>>=20 >>>>>> Only one index at a time can be used per query, so neither strategy i= s >>>>>> optimal. You need at look at the queries you intend to run against >> the >>>>>> system and construct indexes which support them. >>>>>>=20 >>>>>> - md >>>>>>=20 >>>>>> On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins >>>>>> **wrote: >>>>>>=20 >>>>>> Maybe that was a bad example. If the query was name =3D 'Red' what >> index >>>>>>> should I create ? >>>>>>>=20 >>>>>>> Should I create a index of all columns used in each query or have a >>>>>>> index >>>>>>> on individual column ? >>>>>>>=20 >>>>>>>=20 >>>>>>> On 6 Oct 2011, at 17:28, Michael Dykman wrote: >>>>>>>=20 >>>>>>> For the first query, the obvious index on score will give you optima= l >>>>>>> results. >>>>>>>=20 >>>>>>> The second query is founded on this phrase: "Like '%Red%' " and no >> index >>>>>>> will help you there. This is an anti-pattern, I am afraid. The onl= y >>>>>>> way >>>>>>> your database can satisfy that expression is to test each and every >>>>>>> record >>>>>>> in the that database (the test itself being expensive as infix >> finding >>>>>>> is >>>>>>> iterative). Perhaps you should consider this approach instead: >>>>>>> >>>>>> natural-language.html< >> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html> >>>>>>>>=20 >>>>>>> http://dev.mysql.com/doc/**refman/5.5/en/fulltext-** >>>>>>> natural-language.html< >> http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html> >>>>>>>=20 >>>>>>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil<>>>>>> googlemail.com > >>>>>>> neil.tompkins@stripped> wrote: >>>>>>>=20 >>>>>>> Hi, >>>>>>>>=20 >>>>>>>> Can anyone help and offer some advice with regards MySQL indexes. >>>>>>>> Basically >>>>>>>> we have a number of different tables all of which have the obviousl= y >>>>>>>> primary >>>>>>>> keys. We then have some queries using JOIN statements that run >> slowly >>>>>>>> than >>>>>>>> we wanted. How many indexes are recommended per table ? For >> example >>>>>>>> should >>>>>>>> I have a index on all fields that will be used in a WHERE statement= >> ? >>>>>>>> Should the indexes be created with multiple fields ? A example of= >>>>>>>> two >>>>>>>> basic queries >>>>>>>>=20 >>>>>>>> SELECT auto_id, name, score >>>>>>>> FROM test_table >>>>>>>> WHERE score> 10 >>>>>>>> ORDER BY score DESC >>>>>>>>=20 >>>>>>>>=20 >>>>>>>> SELECT auto_id, name, score >>>>>>>> FROM test_table >>>>>>>> WHERE score> 10 >>>>>>>> AND name Like '%Red%' >>>>>>>> ORDER BY score DESC >>>>>>>>=20 >>>>>>>> How many indexes should be created for these two queries ? >>>>>>>>=20 >>>>>>>> Thanks, >>>>>>>> Neil >>>>>>>>=20 >>>>>>>>=20 >>>>>>>=20 >>>>>>>=20 >>>>>>> -- >>>>>>> - michael dykman >>>>>>> -mdykman@**gmail.com >>>>>>>=20 >>>>>>> May the Source be with you. >>>>>>>=20 >>>>>>>=20 >>>>>>>=20 >>>>>>=20 >>>>>>=20 >>>>>=20 >>>>>=20 >>>> -- >>>> MySQL General Mailing List >>>> For list archives: http://lists.mysql.com/mysql >>>> To unsubscribe: >> http://lists.mysql.com/mysql?**unsub=3Dmdykman@stripped< >> http://lists.mysql.com/mysql?unsub=3Dmdykman@stripped> >>>>=20 >>>>=20 >>>=20 >>>=20 >>=20 >> -- >>=20 >> Mit besten Gr=C3=BC=C3=9Fen, Reindl Harald >> the lounge interactive design GmbH >> A-1060 Vienna, Hofm=C3=BChlgasse 17 >> CTO / software-development / cms-solutions >> p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 >> icq: 154546673, http://www.thelounge.net/ >>=20 >> http://www.thelounge.net/signature.asc.what.htm >>=20 >>=20 >=20 >=20 > --=20 > - michael dykman > - mdykman@stripped >=20 > May the Source be with you.