From: Neil Tompkins Date: July 23 2010 5:45am Subject: Re: combined or single indexes? List-Archive: http://lists.mysql.com/mysql/222315 Message-Id: <07270EBA-F055-4E05-8E74-D7568D6B80D5@googlemail.com> MIME-Version: 1.0 (iPod Mail 7E18) Content-Type: multipart/alternative; boundary=Apple-Mail-1-909027624 Content-Transfer-Encoding: 7bit --Apple-Mail-1-909027624 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes Content-Transfer-Encoding: quoted-printable Thanks for the useful information. This is the answer I was. Looking =20 for. Neil On 22 Jul 2010, at 22:25, "Jerry Schwartz" wrote: > From: Tompkins Neil [mailto:neil.tompkins@stripped] > Sent: Thursday, July 22, 2010 4:50 PM > To: Jerry Schwartz > Cc: Shawn Green (MySQL); mysql@stripped > Subject: Re: combined or single indexes? > > > > Thanks for the information Jerry. Just to confirm, you mentioned =20 > "if you only need one key then you only need one key". My question =20= > was that this particular query was using SELECT against a primary =20 > 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 =20 > 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 =20= > don=E2=80=99t remember seeing the original query. The answer lies in = your WH=20 > ERE clause, and in the number of records that would potentially qual=20= > ify. MySQL will ignore keys and do a full table scan if it decides t=20= > hat none of the keys would eliminate a big portion of the records. (=20= > This is why I warned about small sample datasets.) If your query loo=20= > ks like > > > > =E2=80=A6 WHERE `account_num` =3D 17 =E2=80=A6 > > > > and account numbers are unique, then an index on `account_num` =20 > should be enough. If you are always and ONLY searching on the =20 > primary key, then the primary key is all you need. That=E2=80=99s = usually no=20 > t the case, though. You=E2=80=99re probably going to want to search on = other=20 > things, sooner or later. > > > > I=E2=80=99m not an expert on optimizing queries in MySQL, and there = are prob=20 > 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: jerry@stripped > > Web site: www.the-infoshop.com > > > > > > > > Cheers > > Neil > > On Thu, Jul 22, 2010 at 9:27 PM, Jerry Schwartz =20 > 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. =20= > Another > >couple of questions I have : > > > >If I run a EXPLAIN query and SELECT against a primary key and =20 > SELECT fields > >which are not indexed, I assume that returned EXPLAIN statement as =20= > below, > >means I don't need to index additional fields providing the PRIMARY =20= > 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 ";" =20= > at the end > of an EXPLAIN. > > As for the indexing, if you only need one key then you only need one =20= > 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 =20 > 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 =20= > 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: jerry@stripped > Web site: www.the-infoshop.com > > > > > >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 =20 > 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 =20 > previous > >> tables. This is normally not good if the table is the first table =20= > not > >> marked > >> const, and usually very bad in all other cases. Normally, you can =20= > 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 > >> > > > > --Apple-Mail-1-909027624--