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.
