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
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 ?
On Thu, Jul 22, 2010 at 9:27 PM, Jerry Schwartz <jerry@stripped> wrote:
> >-----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
> >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 ?
> [JS] Your posts will be more legible if you use "\G" instead of ";" at the
> of an EXPLAIN.
> As for the indexing, if you only need one key then you only need one key.
> 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
> >how long should I expect this to take ? Is it instant ?
> [JS] Faster than you can type, I should think.
> 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
> >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
> >>> 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
> >> 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