List:Internals« Previous MessageNext Message »
From:Paul Cadach Date:September 6 2001 2:57am
Subject:Re: ORDER BY ... DESC LIMIT 1 optimization ?
View as plain text  
Hi,

> ----- Original Message -----
> From: Fournier Jocelyn [Presence-PC]
> To: Timothy Smith
> Cc: internals@stripped
> Sent: Thursday, September 06, 2001 6:31 AM
> Subject: Re: ORDER BY ... DESC LIMIT 1 optimization ?
>
>
> 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)

As I see your problem is that you don't have any indices at numeropost
column. Just add unique (preferred) or non-unique index to this column and
compare select speed...

[skip]


WBR,
Paul.


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