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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
