From: Gavin Towey Date: October 4 2010 5:27pm Subject: RE: Indexing question List-Archive: http://lists.mysql.com/mysql/223235 Message-Id: <21A67E2153E64D48ACBD190732CB85910A9AC184@site1-mailbox1.pmgi.local> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Include the query, EXPLAIN output, and the relavant SHOW CREATE TABLE table= \G output. Someone should be able to offer suggestions. -----Original Message----- From: Tompkins Neil [mailto:neil.tompkins@stripped] Sent: Monday, October 04, 2010 8:54 AM To: Joerg Bruehe Cc: [MySQL] Subject: Re: Indexing question J=F6rg 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 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 =3D x > Again, I don't know whether MySQL does this, and I refer you to EXPLAIN. > > > HTH, > J=F6rg > > -- > 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 > > This message contains confidential information and is intended only for the= individual named. If you are not the named addressee, you are notified th= at reviewing, disseminating, disclosing, copying or distributing this e-mai= l is strictly prohibited. Please notify the sender immediately by e-mail i= f you have received this e-mail by mistake and delete this e-mail from your= system. E-mail transmission cannot be guaranteed to be secure or error-fre= e as information could be intercepted, corrupted, lost, destroyed, arrive l= ate or incomplete, or contain viruses. The sender therefore does not accept= liability for any loss or damage caused by viruses or errors or omissions = in the contents of this message, which arise as a result of e-mail transmis= sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,= USA, FriendFinder.com