List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:October 4 2010 8:50pm
Subject:Re: Indexing question
View as plain text  
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
Thread
Introduction and InvitationBill Doerrfeld11 Mar
  • Indexing questionJonas Galvez1 Oct
    • RE: Indexing questionGavin Towey1 Oct
      • Re: Indexing questionTompkins Neil3 Oct
        • Re: Indexing questionJoerg Bruehe3 Oct
          • Re: Indexing questionNeil Tompkins3 Oct
            • Re: Indexing questionJoerg Bruehe4 Oct
              • Re: Indexing questionTompkins Neil4 Oct
                • RE: Indexing questionGavin Towey4 Oct
                  • Re: Indexing questionNeil Tompkins4 Oct
                    • Re: Indexing questionTompkins Neil5 Oct
          • Re: Indexing questionNeil Tompkins3 Oct
          • Re: Indexing questionJonas Galvez6 Oct