List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:October 7 2011 7:05pm
Subject:Re: MySQL Indexes
View as plain text  
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.tompkins@stripped>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 <mdykman@stripped> 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.reindl@stripped
> >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 <bphelps@stripped>
> 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.tompkins@stripped>**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<mdykman@stripped>  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.tompkins@stripped>>
> >>>>>>> neil.tompkins@stripped>  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
> >>>>>>> -<mdykman@stripped>mdykman@**gmail.com
> <mdykman@stripped>
> >>>>>>>
> >>>>>>> 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=mdykman@ style="color:#666">stripped<
> >> http://lists.mysql.com/mysql?unsub=1
> >>>>
> >>>>
> >>>
> >>>
> >>
> >> --
> >>
> >> 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
> > - mdykman@stripped
> >
> > May the Source be with you.
>



-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.

Thread
MySQL IndexesTompkins Neil6 Oct
  • Re: MySQL IndexesMichael Dykman6 Oct
    • Re: MySQL IndexesNeil Tompkins6 Oct
      • Re: MySQL IndexesMichael Dykman7 Oct
        • Re: MySQL IndexesNuno Tavares7 Oct
          • Re: MySQL IndexesBrandon Phelps7 Oct
            • Re: MySQL IndexesMichael Dykman7 Oct
              • Re: MySQL IndexesReindl Harald7 Oct
                • Re: MySQL IndexesMichael Dykman7 Oct
                  • Re: MySQL IndexesNeil Tompkins7 Oct
                    • Re: MySQL IndexesMichael Dykman7 Oct
        • Re: MySQL IndexesTompkins Neil7 Oct
          • Re: MySQL IndexesMichael Dykman7 Oct
            • Re: MySQL IndexesNeil Tompkins7 Oct
              • Re: MySQL Indexesmos7 Oct