List:General Discussion« Previous MessageNext Message »
From:Kishore Jalleda Date:February 28 2006 6:58pm
Subject:Help with Query Optimization
View as plain text  
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

Thread
Help with Query OptimizationKishore Jalleda28 Feb
  • Re: Help with Query OptimizationPeter Brawley28 Feb