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:
> 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
> 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.
> 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