List:General Discussion« Previous MessageNext Message »
From:Russell Miller Date:October 6 2001 3:34am
Subject:Re: Bizarre query performance
View as plain text  
The fact that the explain didn't say "Index used".

--Russell

----- Original Message -----
From: "Philip Brown" <phil@stripped>
To: "Russell Miller" <duskglow2000@stripped>; <mysql@stripped>
Sent: Friday, October 05, 2001 4:00 PM
Subject: RE: Bizarre query performance


> > It doesn't look like you are using an index.  Have you tried creating
one
> > and seeing what the effect on execution time is?
>
> What makes you think the index isn't being used?
> EXPLAIN SELECT * FROM X WHERE (PRIMARY KEY) = N looks like it is using
> the primary key. However, just to check I created a specific unique index
> on that value but performance was the same.
>
> The second query is using the index on ScheduledStart, which is correct
> and fine. That query works fine. It looks like the queries on the primary
> key are the one's that have odd behaviour.
>
> >
> > --Russell
> >
> > ----- Original Message -----
> > From: "Philip Brown" <phil@stripped>
> > To: "Russell Miller" <duskglow2000@stripped>;
> <mysql@stripped>
> > Sent: Friday, October 05, 2001 1:34 PM
> > Subject: RE: Bizarre query performance
> >
> >
> > > > Have you tried "explain"ing the two select to see where all
> > the time is
> > > > being spent and how the queries are optimized?
> > >
> > > Sorry, I should have included that in my detail.
> > >
> > >
> > +-------+-------+---------------+---------+---------+-------+-----
> > -+-------+
> > > | table | type  | possible_keys | key     | key_len | ref   |
> > rows | Extra
> > |
> > >
> > +-------+-------+---------------+---------+---------+-------+-----
> > -+-------+
> > > | X     | const | PRIMARY,p1    | PRIMARY |       4 | const |    1 |
> > |
> > >
> > +-------+-------+---------------+---------+---------+-------+-----
> > -+-------+
> > >
> > > This is the query that takes 200ms. It performs the same
> > regardless of the
> > > particular table involved (all have a similar primary key), or
> > the record
> > > fetched.
> > >
> > >
> > +------+-------+----------------+----------------+---------+------
> > +------+--
> > > ----------+
> > > | table| type  | possible_keys  | key            | key_len |
> > ref  | rows |
> > > Extra      |
> > >
> > +------+-------+----------------+----------------+---------+------
> > +------+--
> > > ----------+
> > > | X    | range | ScheduledStart | ScheduledStart |       8 |
> > NULL |   25 |
> > > where used |
> > >
> > +------+-------+----------------+----------------+---------+------
> > +------+--
> > > ----------+
> > >
> > > This is the query that takes less than 10ms. Performance should be
worse
> > > than that
> > > above, but it is not.
> >
> >
> > _________________________________________________________
> > Do You Yahoo!?
> > Get your free @yahoo.com address at http://mail.yahoo.com
> >
> >


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Thread
Bizarre query performancePhilip Brown5 Oct
  • Re: Bizarre query performanceRussell Miller5 Oct
    • RE: Bizarre query performancePhilip Brown5 Oct
      • Re: Bizarre query performanceRussell Miller5 Oct
        • RE: Bizarre query performancePhilip Brown5 Oct
          • Re: Bizarre query performanceRussell Miller6 Oct
            • RE: Bizarre query performancePhilip Brown6 Oct
  • Re: Bizarre query performanceBoyd Lynn Gerber5 Oct
    • RE: Bizarre query performancePhilip Brown5 Oct
      • RE: Bizarre query performanceBoyd Lynn Gerber5 Oct
  • Re: Bizarre query performanceDan Nelson5 Oct
    • RE: Bizarre query performancePhilip Brown5 Oct
      • Re: Bizarre query performanceDan Nelson5 Oct
        • Re: Bizarre query performanceBoyd Lynn Gerber6 Oct
          • RE: Bizarre query performancePhilip Brown6 Oct
            • Re: Bizarre query performanceDan Nelson7 Oct
              • Preventing Nagle with mysql (was: Bizarre query performance)Philip Brown8 Oct
                • Re: Preventing Nagle with mysql (was: Bizarre query performance)Dan Nelson10 Oct
                  • Re: Preventing Nagle with mysql (was: Bizarre query performance)Sinisa Milivojevic10 Oct