List:Internals« Previous MessageNext Message »
From:Fournier Jocelyn [Presence-PC] Date:September 6 2001 6:06am
Subject:Re: ORDER BY ... DESC LIMIT 1 optimization ?
View as plain text  
Unfortunately it doesn't change anything, mysql still use filesorting for
this query :(

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,numeropost | PRIMARY |       3 | const
| 6504 | where used; Using filesort |
+-------------------+------+--------------------+---------+---------+-------
+------+----------------------------+
1 row in set (0.03 sec)

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

Moreover I have index on (numeropost, numreponse) and on (numreponse), so it
should be optimised for query on 'numeropost', 'numeropost AND numreponse',
and  'numreponse' (if I understand how index work).

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


> 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