From: Neil Tompkins
Date: October 4 2010 8:50pm
Subject: Re: Indexing question
List-Archive: http://lists.mysql.com/mysql/223238
Message-Id: <2B844872-E911-4ABD-9135-BA758A42A53E@googlemail.com>
MIME-Version: 1.0 (iPod Mail 7E18)
Content-Type: text/plain;
	charset=utf-8;
	format=flowed;
	delsp=yes
Content-Transfer-Encoding: quoted-printable

I've got a fair few number of queries to be checked over. Will send =20
them tommorrow

On 4 Oct 2010, at 18:27, Gavin Towey <gtowey@stripped> wrote:

> Include the query, EXPLAIN output, and the relavant SHOW CREATE =20
> 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=C3=B6rg
>
> Thanks for the useful reply.  Maybe I can EXPLAIN my select queries =20=

> for you
> to advise if any changes need to be made ?
>
> Regards
> Neil
>
> On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe =20
> <joerg.bruehe@stripped>wrote:
>
>> Hi!
>>
>>
>> Neil Tompkins wrote:
>>> Thanks for your reply. So should we create individual indexes on =20
>>> 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 =20=

>> 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 =20
>> 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 =20=

>> 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) =20
>> create
>> one that provides less gain in searches than it costs in data =20
>> 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 =20
>> depends
>> on the searches executed by your commands, their relative =20
>> 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 =20
>> 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 =20=

>> will
>> not help in general.
>>
>> In database implementations, there is the concept of a "covering =20
>> 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 =20
>> EXPLAIN.
>>
>>
>> HTH,
>> J=C3=B6rg
>>
>> --
>> Joerg Bruehe,  MySQL Build Team,  joerg.bruehe@stripped
>> ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099 =20
>> Berlin
>> Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. =20=

>> Ven
>> Amtsgericht Muenchen: HRA 95603
>>
>>
>
> This message contains confidential information and is intended only =20=

> for the individual named.  If you are not the named addressee, you =20
> are notified that reviewing, disseminating, disclosing, copying or =20
> distributing this e-mail is strictly prohibited.  Please notify the =20=

> sender immediately by e-mail if you have received this e-mail by =20
> mistake and delete this e-mail from your system. E-mail transmission =20=

> cannot be guaranteed to be secure or error-free as information could =20=

> be intercepted, corrupted, lost, destroyed, arrive late or =20
> incomplete, or contain viruses. The sender therefore does not accept =20=

> liability for any loss or damage caused by viruses or errors or =20
> omissions in the contents of this message, which arise as a result =20
> of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt =20
> court, Sunnyvale, CA 94089, USA, FriendFinder.com