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

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


> It doesn't change anything, the query is still slow.

Can you show results of:
    select count(*) from threadhardwarefr7
and
    select count(*) from threadhardwarefr7 where numeropost=5347

May be better to do some optimizations or additional fast queries to
minimize selection count? One solution is to have additional table, say
threadhardwarefr7_stats, with fields numeropost and max_numresponse, which
updates when new post is done with next query:
    update threadhardwarefr7_stats set max_numresponse=<current_reponse_id>
where numeropost=<value for this post>
then you do next selects:
    select max_numresponse from threadhardwarefr7_stats where
numeropost=<value of numeropost>
and
    SELECT contenu,pseudo,signature FROM threadhardwarefr7 WHERE
numeropost=<value of numeropost> and num_response=<value of previous select>

At least this will be fastest as possible (unique index on
(numeropost,num_response) must be exists to fetch single row with previous
query fast).


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