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
|
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
