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

Next, try to add numresponse column into select output...

Also, don't see so much to explains, check real selection times...

Filesort will be used AFTER selection of records with numeropost=<value>
will be done, so, this will be small bunch of records and it usually
(depends on number of records in the table) will be faster than look for
specific value of numeropost without index.


WBR,
Paul.

----- Original Message -----
From: Fournier Jocelyn [Presence-PC]
To: Paul Cadach
Cc: internals@stripped
Sent: Thursday, September 06, 2001 1:06 PM
Subject: Re: ORDER BY ... DESC LIMIT 1 optimization ?


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