List:Internals« Previous MessageNext Message »
From:Fournier Jocelyn [Presence-PC] Date:September 5 2001 11:31pm
Subject:Re: ORDER BY ... DESC LIMIT 1 optimization ?
View as plain text  
BTW, here is the EXPLAIN + INDEX structure :

mysql> EXPLAIN SELECT contenu,pseudo,signature FROM threadhardwarefr7 WHERE
numeropost=5347
    -> ORDER BY numreponse DESC LIMIT 1;
+-------------------+------+---------------+---------+---------+-------+----
--+----------------------------+
| table             | type | possible_keys | key     | key_len | ref   |
rows | Extra                      |
+-------------------+------+---------------+---------+---------+-------+----
--+----------------------------+
| threadhardwarefr7 | ref  | PRIMARY       | PRIMARY |       3 | const |
6475 | where used; Using filesort |
+-------------------+------+---------------+---------+---------+-------+----
--+----------------------------+
1 row in set (0.00 sec)

mysql> SHOW INDEX FROM threadhardwarefr7;
+-------------------+------------+------------+--------------+-------------+
-----------+-------------+----------+--------+---------+
| Table             | Non_unique | Key_name   | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+-------------------+------------+------------+--------------+-------------+
-----------+-------------+----------+--------+---------+
| threadhardwarefr7 |          0 | PRIMARY    |            1 | numeropost  |
A         |       15054 |     NULL | NULL   |         |
| threadhardwarefr7 |          0 | PRIMARY    |            2 | numreponse  |
A         |      451635 |     NULL | NULL   |         |
| threadhardwarefr7 |          0 | numreponse |            1 | numreponse  |
A         |      451635 |     NULL | NULL   |         |
| threadhardwarefr7 |          1 | ip         |            1 | ip          |
A         |       10264 |     NULL | NULL   |         |
| threadhardwarefr7 |          1 | date       |            1 | date        |
A         |      451635 |     NULL | NULL   |         |
| threadhardwarefr7 |          1 | pseudo     |            1 | pseudo      |
A         |        3961 |     NULL | NULL   |         |
+-------------------+------------+------------+--------------+-------------+
-----------+-------------+----------+--------+---------+
6 rows in set (0.00 sec)


----- Original Message -----
From: "Timothy Smith" <tim@stripped>
To: "Fournier Jocelyn [Presence-PC]" <joc@stripped>
Cc: <internals@stripped>
Sent: Thursday, September 06, 2001 1:10 AM
Subject: Re: ORDER BY ... DESC LIMIT 1 optimization ?


> On 2001 Sep 05, Fournier Jocelyn [Presence-PC] <joc@stripped>
wrote:
> > Won't it be possible the MySQL parser optimize the SELECT y FROM x ORDER
BY
> > z DESC LIMIT 1 as :
>
> MySQL 4.0 already has optimized this type of query.  I don't
> think your solution would be general enough, but it is a good
> work around for the time being if you are using MySQL 3.23.
>
> Tim
>
> --
>    __  ___     ___ ____  __
>   /  |/  /_ __/ __/ __ \/ /    Tim Smith <tim@stripped>
>  / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-time Developer
> /_/  /_/\_, /___/\___\_\___/   Boone, NC  USA
>        <___/   www.mysql.com
>
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
> To request this thread, e-mail internals-thread1688@stripped
> To unsubscribe, e-mail <internals-unsubscribe@stripped>
>
>

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