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