List:General Discussion« Previous MessageNext Message »
From:Philip Brown Date:October 5 2001 9:00pm
Subject:RE: Bizarre query performance
View as plain text  
> 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
>
>

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