List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:July 22 2010 7:38pm
Subject:Re: combined or single indexes?
View as plain text  
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,,

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 ?

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