List:Internals« Previous MessageNext Message »
From:Fournier Jocelyn [Presence-PC] Date:September 5 2001 8:41pm
Subject:ORDER BY ... DESC LIMIT 1 optimization ?
View as plain text  
Hi,

Won't it be possible the MySQL parser optimize the SELECT y FROM x ORDER BY
z DESC LIMIT 1 as :

SELECT MAX(z) FROM x;
followed by :
SELECT y FROM x WHERE z=a;

(where 'a' is the result of the first SELECT MAX(z) query)


The speed gain is quite apreciable when there is a lot of rows :

(# Time: 010905 22:14:00
# User@Host: mysql[mysql] @ localhost []
# Time: 9  Lock_time: 0  Rows_sent: 1  Rows_examined: 7718
SELECT contenu,pseudo,signature FROM threadhardwarefr7 WHERE numeropost=5347
ORDER BY numreponse DESC LIMIT 1;)


mysql> SELECT contenu,pseudo,signature FROM threadhardwarefr7 WHERE
numeropost=5347 ORDER BY numreponse DESC LIMIT 1;
+------------------------------------------+-------------+-----------+
| contenu                                  | pseudo      | signature |
+------------------------------------------+-------------+-----------+
| umff > :hello:

meme kestion que sad  | Biff Tannen |         1 |
+------------------------------------------+-------------+-----------+
1 row in set (8.29 sec)


mysql> SELECT MAX(numreponse) FROM threadhardwarefr7 WHERE numeropost=5347;
+-----------------+
| MAX(numreponse) |
+-----------------+
|         1032001 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT contenu,pseudo,signature FROM threadhardwarefr7 WHERE
numreponse=1032001;
+------------------------------------------+-------------+-----------+
| contenu                                  | pseudo      | signature |
+------------------------------------------+-------------+-----------+
| umff > :hello:

meme kestion que sad  | Biff Tannen |         1 |
+------------------------------------------+-------------+-----------+
1 row in set (0.00 sec)

What do you think about this ?


Jocelyn Fournier
Presence-PC

Thread
ORDER BY ... DESC LIMIT 1 optimization ?Fournier Jocelyn [Presence-PC]5 Sep
  • Re: ORDER BY ... DESC LIMIT 1 optimization ?Timothy Smith6 Sep
  • Re: ORDER BY ... DESC LIMIT 1 optimization ?Fournier Jocelyn [Presence-PC]6 Sep
  • Re: ORDER BY ... DESC LIMIT 1 optimization ?Fournier Jocelyn [Presence-PC]6 Sep
  • ORDER BY ... DESC LIMIT 1 optimization ?Michael Widenius6 Sep
  • Re: ORDER BY ... DESC LIMIT 1 optimization ?Paul Cadach6 Sep
  • Re: ORDER BY ... DESC LIMIT 1 optimization ?Fournier Jocelyn [Presence-PC]6 Sep
  • Re: ORDER BY ... DESC LIMIT 1 optimization ?Paul Cadach6 Sep
  • Re: ORDER BY ... DESC LIMIT 1 optimization ?Fournier Jocelyn [Presence-PC]6 Sep
    • Re: ORDER BY ... DESC LIMIT 1 optimization ?Timothy Smith6 Sep
      • Re: ORDER BY ... DESC LIMIT 1 optimization ?Michael Widenius6 Sep
      • Re: ORDER BY ... DESC LIMIT 1 optimization ?Michael Widenius16 Jan
  • Re: ORDER BY ... DESC LIMIT 1 optimization ?Paul Cadach6 Sep