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 ? 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 > >> > > > >
