I've got a fair few number of queries to be checked over. Will send
them tommorrow
On 4 Oct 2010, at 18:27, Gavin Towey <gtowey@stripped> wrote:
> 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ö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.bruehe@stripped>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.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 that reviewing, disseminating, disclosing, copying or
> distributing this e-mail is strictly prohibited. Please notify the
> sender immediately by e-mail if 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-free as information could
> be intercepted, corrupted, lost, destroyed, arrive late 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 transmission. [FriendFinder Networks, Inc., 220 Humbolt
> court, Sunnyvale, CA 94089, USA, FriendFinder.com
