Jörg Thanks for the useful reply. Maybe I can EXPLAIN my select queries for you to advise if any changes need to be made ?
Regards Neil On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe <joerg.bru...@oracle.com>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 > 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.bru...@oracle.com > 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 > >
