List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 28 2006 8:02pm
Subject:Re: Help with Query Optimization
View as plain text  
Kishore,

 >         We use Vbulletin for our message boards, and I have a query which
 >takes approximately 1.2 - 1.5 seconds to execute, on a table ('thread')
 >having around 130,000 rows, it is as follows

 >SELECT threadid
 >FROM thread
 >LEFT JOIN deletionlog
 >ON ( thread.threadid = deletionlog.primaryid AND TYPE = 'thread' )
 >WHERE forumid = 98
 >  AND sticky = 0
 >  AND visible = 1
 >  AND deletionlog.primaryid IS NULL
 >ORDER BY sticky DESC , lastpost DESC
 >LIMIT 0 , 15

Your formatting of EXPLAIN output is a bit hard to read, but did you try 
indexes on
(sticky,lastpost) and (forumid,sticky,visible)? And depending on how big 
deletionlog is,
you might move ...
     AND deletionlog.primaryid IS NULL
from the WHERE clause to the ON clause, to reduce the number of rows the 
query engine
has to write to its temp table.

PB

-----

Kishore Jalleda wrote:
> Hi All,
>          We use Vbulletin for our message boards, and I have a query which
> takes approximately 1.2 - 1.5 seconds to execute, on a table ('thread')
> having around 130,000 rows, it is as follows
>
> SELECT threadid
> FROM thread AS thread
> LEFT JOIN deletionlog AS deletionlog ON ( thread.threadid = deletionlog.
> primaryid
> AND TYPE = 'thread' )
> WHERE forumid =98
> AND sticky =0
> AND visible =1
> AND deletionlog.primaryid IS NULL
> ORDER BY sticky DESC , lastpost DESC
> LIMIT 0 , 15
>
> There is an index on the field 'lastpost' ( Type: INT(10) ) , also Explain
> Select gives me this......
>
>    id
>  select_type
>  table
>  type
>  possible_keys
>  key
>  key_len
>  ref
>  rows
>  Extra
>  1
>  SIMPLE
>  thread
>  index
>  *NULL*
>  lastpost
>  4
>  *NULL*
>  112783
>  Using where
>  1
>  SIMPLE
>  deletionlog
>  eq_ref
>  PRIMARY
>  PRIMARY
>  5
>  foxbox16.thread.threadid,const
>  1
>  Using where; Using index; Not exists
>
>
> I am no SQL guru so can somebody kindly help me to optimize this query so
> that it could run faster , as during peak times this slows the DB down a
> lot..........
>
> Kishore Jalleda
>
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
Thread
Help with Query OptimizationKishore Jalleda28 Feb
  • Re: Help with Query OptimizationPeter Brawley28 Feb