List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:October 4 2010 2:38pm
Subject:Re: Indexing question
View as plain text  
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
   SELECT SUM(B) WHERE A = x
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

Thread
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