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> > > -- - michael dykman - mdyk...@gmail.com May the Source be with you.
