List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 2 2000 7:07pm
Subject:One more unoptimal behavior
View as plain text  

Hi!

> There is a table and a multiple column key defined on columns counter,id =
> =20
> 
> mysql> select ts,id from st01.g08hits where counter_id=3D2755 and =
> ts>19921291229153409 and id>82309 order by ts;
> 
> mysql> select ts,id from st01.g08hits where counter_id=3D2755 and =
> ts>19921291229153409 and id>82309 order by counter_id,ts;
> 
> return exactly the same result as counter_id=3DCONST but second one runs =
> much faster (espetialy then adding LIMIT XX).  The thing is MYSQL =
> probably does not use
> the index for sort in the first query as the order by is not on the =
> leading part of the key.
> 
> This is not  a bug but just one more optimization point. :)
> 
> Best Regards
>                        Peter Zaitsev

The problem is that it's not trivial in all cases to notice that
'counter_id' is a constant for your result set.  We have however
already this optimization idea in our TODO folder and we will look
at this at some point.

Regards,
Monty

PS: By the way, I have a hard time reading your mails;  Your mailer
    (Microsoft outlook) doesn't produce mails in the proper mime
    format and this makes some things a real pain...
Thread
One more unoptimal behaviorPeter Zaitsev2 Jan
  • One more unoptimal behaviorMichael Widenius2 Jan