MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:October 4 2010 3:54pm
Subject:Re: Indexing question
View as plain text  

Thanks for the useful reply.  Maybe I can EXPLAIN my select queries for you
to advise if any changes need to be made ?


On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe <joerg.bruehe@stripped>wrote:

> Hi!
> Neil Tompkins wrote:
> > Thanks for your reply. So should we create individual indexes on each
> > field or a multiple column index ??
> This question cannot be answered without checking and measuring your
> installation. The decision whether to create an index is always an act
> of balancing:
> - If there is an index, the database server can use it to find data
>  records by looking up the index, not scanning the base data.
>  This results in load reduction (both CPU and disk IO) and speeds up
>  query execution.
> - If there is an index, the database server must maintain it whenever
>  data are altered (insert/update/delete), in addition to the base data.
>  This is increased load (both CPU and disk IO) and slows down data
>  changes.
> So obviously you want to create only those indexes that are helpful for
> query execution: you will never (voluntarily) create an index on a
> column which isn't used in search conditions, or whose use is already
> provided by other indexes.
> Of the remaining candidate indexes, you will never (voluntarily) create
> one that provides less gain in searches than it costs in data changes.
> With MySQL, AFAIK there is the limitation that on one table only one
> index can be used. As a result, the choice of indexes to create depends
> on the searches executed by your commands, their relative frequency, and
> the frequency of data changes.
> To answer your other question: If you run aggregate functions (like
> SUM(), MIN(), or MAX()) on all records of a table, their results could
> be computed by accessing a matching index only. I don't know whether
> MySQL does this, I propose you check that yourself using EXPLAIN.
> If you run them on subsets of a table only, an index on that column will
> not help in general.
> In database implementations, there is the concept of a "covering index":
> If you have an index on columns A and B of some table, its contents
> (without the base data) would suffice to answer
> Again, I don't know whether MySQL does this, and I refer you to EXPLAIN.
> HTH,
> Jörg
> --
> Joerg Bruehe,  MySQL Build Team,  joerg.bruehe@stripped
> ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099 Berlin
> Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
> Amtsgericht Muenchen: HRA 95603

Introduction and InvitationBill Doerrfeld11 Mar
  • Indexing questionJonas Galvez1 Oct
    • RE: Indexing questionGavin Towey1 Oct
      • Re: Indexing questionTompkins Neil3 Oct
        • Re: Indexing questionJoerg Bruehe3 Oct
          • Re: Indexing questionNeil Tompkins3 Oct
            • Re: Indexing questionJoerg Bruehe4 Oct
              • Re: Indexing questionTompkins Neil4 Oct
                • RE: Indexing questionGavin Towey4 Oct
                  • Re: Indexing questionNeil Tompkins4 Oct
                    • Re: Indexing questionTompkins Neil5 Oct
          • Re: Indexing questionNeil Tompkins3 Oct
          • Re: Indexing questionJonas Galvez6 Oct