List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:July 22 2010 8:27pm
Subject:RE: combined or single indexes?
View as plain text  
>-----Original Message-----
>From: Tompkins Neil [mailto:neil.tompkins@stripped]
>Sent: Thursday, July 22, 2010 3:39 PM
>To: Shawn Green (MySQL)
>Cc: mysql@stripped
>Subject: Re: combined or single indexes?
>
>Thanks for your reply, and sorry for not verifying in the manual.  Another
>couple of questions I have :
>
>If I run a EXPLAIN query and SELECT against a primary key and SELECT fields
>which are not indexed, I assume that returned EXPLAIN statement as below,
>means I don't need to index additional fields providing the PRIMARY KEY is
>included in the SELECT statement ?
>
>table,type,possible_keys,key,key_len,ref,rows,Extra,
>Products,const,PRIMARY,PRIMARY,8,const,1,,
>
[JS] Your posts will be more legible if you use "\G" instead of ";" at the end 
of an EXPLAIN.

As for the indexing, if you only need one key then you only need one key. Just 
remember that when you test things with sample data, MySQL might make 
surprising decisions based upon the amount of data. You'll only really know 
what will happen if you have a substantial data set.

>Also, if I want to add a index to an existing table containing 9000 records,
>how long should I expect this to take ?  Is it instant ?
>
[JS] Faster than you can type, I should think.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@stripped
Web site: www.the-infoshop.com



>Cheers
>Neil
>
>
>On Thu, Jul 22, 2010 at 5:20 PM, Shawn Green (MySQL) <
>shawn.l.green@stripped> wrote:
>
>> On 7/21/2010 1:02 PM, Tompkins Neil wrote:
>>
>>> Hi
>>>
>>> So Just running a basic query I get returned the following :
>>>
>>> table,type,possible_keys,key,key_len,ref,rows,Extra,
>>> Products,ALL,,,,,9884,where used,
>>>
>>> Therefore, I assume "*ALL*" is the worst possible type and should look at
>>> adding a an index to this particular field ?
>>>
>>>
>> Why assume when the manual is right there to remove all doubt?
>>
>> http://dev.mysql.com/doc/refman/5.1/en/explain-output.html
>> ####
>> ALL
>>
>> A full table scan is done for each combination of rows from the previous
>> tables. This is normally not good if the table is the first table not 
>> marked
>> const, and usually very bad in all other cases. Normally, you can avoid ALL
>> by adding indexes that enable row retrieval from the table based on 
>> constant
>> values or column values from earlier tables.
>> ####
>>
>>
>>
>>
>> --
>> Shawn Green
>> MySQL Principle Technical Support Engineer
>> Oracle USA, Inc.
>> Office: Blountville, TN
>>



Thread
combined or single indexes?Bryan Cantwell6 Jul
  • Re: combined or single indexes?Joerg Bruehe6 Jul
  • Re: combined or single indexes?Octavian Rasnita7 Jul
    • Re: combined or single indexes?Neil Tompkins8 Jul
      • Re: combined or single indexes?Johan De Meersman9 Jul
        • Re: combined or single indexes?Neil Tompkins9 Jul
          • Re: combined or single indexes?Johan De Meersman9 Jul
            • Re: combined or single indexes?mos9 Jul
              • Re: combined or single indexes?Tompkins Neil21 Jul
                • Re: combined or single indexes?MySQL)22 Jul
                  • Re: combined or single indexes?Tompkins Neil22 Jul
                    • RE: combined or single indexes?Jerry Schwartz22 Jul
                      • Re: combined or single indexes?Tompkins Neil22 Jul
                        • RE: combined or single indexes?Jerry Schwartz22 Jul
                          • Re: combined or single indexes?Neil Tompkins23 Jul