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