From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Thursday, July 22, 2010 4:50 PM
To: Jerry Schwartz
Cc: Shawn Green (MySQL); mysql@lists.mysql.com
Subject: Re: combined or single indexes?
Thanks for the information Jerry. Just to confirm, you mentioned
"if you only need one key then you only need one key". My question
was that this particular query was using SELECT against a primary
key and other fields which are NOT indexed. The EXPLAIN result was
table,type,possible_keys,key,key_len,ref,rows,Extra,
Products,const,PRIMARY,PRIMARY,8,const,1,,
So from this do I assume that if I'm always searching the PRIMARY
KEY, that I don't need to index the other fields ?
[JS] I think I must have missed the start of this thread, because I
don’t remember seeing the original query. The answer lies in your WH
ERE clause, and in the number of records that would potentially qual
ify. MySQL will ignore keys and do a full table scan if it decides t
hat none of the keys would eliminate a big portion of the records. (
This is why I warned about small sample datasets.) If your query loo
ks like
… WHERE `account_num` = 17 …
and account numbers are unique, then an index on `account_num`
should be enough. If you are always and ONLY searching on the
primary key, then the primary key is all you need. That’s usually no
t the case, though. You’re probably going to want to search on other
things, sooner or later.
I’m not an expert on optimizing queries in MySQL, and there are prob
ably differences between the storage engines, but I hope this helps.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
Cheers
Neil
On Thu, Jul 22, 2010 at 9:27 PM, Jerry Schwartz <je...@gii.co.jp>
wrote:
>-----Original Message-----
>From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
>Sent: Thursday, July 22, 2010 3:39 PM
>To: Shawn Green (MySQL)
>Cc: mysql@lists.mysql.com
>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: je...@gii.co.jp
Web site: www.the-infoshop.com
>Cheers
>Neil
>
>
>On Thu, Jul 22, 2010 at 5:20 PM, Shawn Green (MySQL) <
>shawn.l.gr...@oracle.com> 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
>>
