List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:October 7 2011 5:24pm
Subject:Re: MySQL Indexes
View as plain text  
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.

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