List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:July 23 2010 5:45am
Subject:Re: combined or single indexes?
View as plain text  
Thanks for the useful information. This is the answer I was. Looking  
for.

Neil

On 22 Jul 2010, at 22:25, "Jerry Schwartz" <jerry@stripped> 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  
> "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: jerry@stripped
>
> Web site: www.the-infoshop.com
>
>
>
>
>
>
>
> Cheers
>
> Neil
>
> 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 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: 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  
> 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
> >>
>
>
>
>

Thread
combined or single indexes?Bryan Cantwell6 Jul
  • Re: combined or single indexes?Joerg Bruehe6 Jul
  • Re: combined or single indexes?Octavian Rasnita7 Jul
    • Re: combined or single indexes?Neil Tompkins8 Jul
      • Re: combined or single indexes?Johan De Meersman9 Jul
        • Re: combined or single indexes?Neil Tompkins9 Jul
          • Re: combined or single indexes?Johan De Meersman9 Jul
            • Re: combined or single indexes?mos9 Jul
              • Re: combined or single indexes?Tompkins Neil21 Jul
                • Re: combined or single indexes?MySQL)22 Jul
                  • Re: combined or single indexes?Tompkins Neil22 Jul
                    • RE: combined or single indexes?Jerry Schwartz22 Jul
                      • Re: combined or single indexes?Tompkins Neil22 Jul
                        • RE: combined or single indexes?Jerry Schwartz22 Jul
                          • Re: combined or single indexes?Neil Tompkins23 Jul