The second index you specified '(field_b, field_a)' would be usable when querying on field_b alone, or both fields in conjunction. This particular index is of no value should you be querying 'field_a' alone. Then that first index '(field_a, field_b)' would apply.
- md On Fri, Oct 7, 2011 at 2:55 PM, Neil Tompkins <neil.tompk...@googlemail.com>wrote: > Can you give more information as to why the second index would be of no use > ? > > On 7 Oct 2011, at 18:24, Michael Dykman <mdyk...@gmail.com> 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. > > > > - md > > > > On Fri, Oct 7, 2011 at 12:20 PM, Reindl Harald <h.rei...@thelounge.net > >wrote: > > > >> but could this not be called a bug? > >> > >> 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. > >>> > >>> On Fri, Oct 7, 2011 at 10:49 AM, Brandon Phelps <bphe...@gls.com> > wrote: > >>> > >>>> 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)? > >>>> > >>>> > >>>> On 10/06/2011 07:43 PM, Nuno Tavares wrote: > >>>> > >>>>> Neil, whenever you see multiple fields you'd like to index, you > should > >>>>> consider, at least: > >>>>> > >>>>> * The frequency of each query; > >>>>> * The occurrences of the same field in multiple queries; > >>>>> * The cardinality of each field; > >>>>> > >>>>> 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.... > >>>>> > >>>>> 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). > >>>>> > >>>>> -NT > >>>>> > >>>>> > >>>>> Em 07-10-2011 00:22, Michael Dykman escreveu: > >>>>> > >>>>>> Only one index at a time can be used per query, so neither strategy > is > >>>>>> optimal. You need at look at the queries you intend to run against > >> the > >>>>>> system and construct indexes which support them. > >>>>>> > >>>>>> - md > >>>>>> > >>>>>> On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins > >>>>>> <neil.tompk...@googlemail.com>**wrote: > >>>>>> > >>>>>> Maybe that was a bad example. If the query was name = 'Red' what > >> index > >>>>>>> should I create ? > >>>>>>> > >>>>>>> Should I create a index of all columns used in each query or have a > >>>>>>> index > >>>>>>> on individual column ? > >>>>>>> > >>>>>>> > >>>>>>> On 6 Oct 2011, at 17:28, Michael Dykman<mdyk...@gmail.com> wrote: > >>>>>>> > >>>>>>> For the first query, the obvious index on score will give you > optimal > >>>>>>> results. > >>>>>>> > >>>>>>> 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 > only > >>>>>>> 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: > >>>>>>> <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> > >>>>>>>> > >>>>>>> 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> > >>>>>>> > >>>>>>> On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil<<neil.tompkins@** > >>>>>>> googlemail.com <neil.tompk...@googlemail.com>> > >>>>>>> neil.tompk...@googlemail.com> wrote: > >>>>>>> > >>>>>>> Hi, > >>>>>>>> > >>>>>>>> Can anyone help and offer some advice with regards MySQL indexes. > >>>>>>>> Basically > >>>>>>>> we have a number of different tables all of which have the > obviously > >>>>>>>> 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 > >>>>>>>> > >>>>>>>> SELECT auto_id, name, score > >>>>>>>> FROM test_table > >>>>>>>> WHERE score> 10 > >>>>>>>> ORDER BY score DESC > >>>>>>>> > >>>>>>>> > >>>>>>>> SELECT auto_id, name, score > >>>>>>>> FROM test_table > >>>>>>>> WHERE score> 10 > >>>>>>>> AND name Like '%Red%' > >>>>>>>> ORDER BY score DESC > >>>>>>>> > >>>>>>>> How many indexes should be created for these two queries ? > >>>>>>>> > >>>>>>>> Thanks, > >>>>>>>> Neil > >>>>>>>> > >>>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> -- > >>>>>>> - michael dykman > >>>>>>> -<mdyk...@gmail.com>mdykman@**gmail.com <mdyk...@gmail.com> > >>>>>>> > >>>>>>> May the Source be with you. > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>> > >>>>>> > >>>>> > >>>>> > >>>> -- > >>>> MySQL General Mailing List > >>>> For list archives: http://lists.mysql.com/mysql > >>>> To unsubscribe: > >> http://lists.mysql.com/mysql?**unsub=mdyk...@gmail.com< > >> http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com> > >>>> > >>>> > >>> > >>> > >> > >> -- > >> > >> Mit besten Grüßen, Reindl Harald > >> the lounge interactive design GmbH > >> A-1060 Vienna, Hofmühlgasse 17 > >> CTO / software-development / cms-solutions > >> p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 > >> icq: 154546673, http://www.thelounge.net/ > >> > >> http://www.thelounge.net/signature.asc.what.htm > >> > >> > > > > > > -- > > - michael dykman > > - mdyk...@gmail.com > > > > May the Source be with you. > -- - michael dykman - mdyk...@gmail.com May the Source be with you.
