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
signature.asc
Description: OpenPGP digital signature
