List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:July 22 2010 8:50pm
Subject:Re: combined or single indexes?
View as plain text  
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 <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