List:Internals« Previous MessageNext Message »
From:Fournier Jocelyn [Presence-PC] Date:September 6 2001 6:26am
Subject:Re: ORDER BY ... DESC LIMIT 1 optimization ?
View as plain text  
It doesn't change anything, the query is still slow.

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


> 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