|
|
Posted by Anze on 12/06/07 13:13
Hi!
I'm having problems optimizing a slow query - I have tried to understand the
output of explain and everything, but I guess I'm missing something. I would
appreciate if someone could help me out...
Update: while writing this question I have (probably) figured out where the
problem lies, but I would still appreciate a comment on how to solve it...
The query should take 5 latest posts from vBulletin:
-----
select
p.postid, t.title 'ttitle', p.title 'ptitle', p.pagetext 'text'
from
thread t, post p
where
p.threadid=t.threadid and
t.visible=1 and
t.open=1
order by
p.postid desc limit 5;
-----
Slow queries log tells me this:
# Time: 071127 16:26:41
# Query_time: 4 Lock_time: 0 Rows_sent: 5 Rows_examined: 252099
use mydb;
select p.postid, t.title 'ttitle', p.title 'ptitle', p.pagetext 'text' from
thread t, post p where p.threadid=t.threadid and t.visible=1 and t.open=1
order by p.postid desc limit 5;
There lies the problem - 250000 rows examined... It must not be using index.
Let's see what explain says:
------------------------------------------------------------------------
id select_type table type possible_keys key key_len
1 SIMPLE t ALL PRIMARY NULL NULL
1 SIMPLE p ref threadid threadid 4
------------------------------------------------------------------------
------------------------------------------------------------------------
ref rows Extra
NULL 3356 Using where; Using temporary; Using filesort
mydb.t.threadid 3
------------------------------------------------------------------------
I would guess the problem lies in the select from table "thread", but we are
selecting by threadid which is primary key on that table.
Problem: MySQL is first performing a join on the table, then ordering, then
limiting results to 5 latest - which is of course very slow. This would be
the solution:
SELECT title
FROM thread
WHERE threadid
IN (
SELECT threadid
FROM post
ORDER BY postid DESC
LIMIT 5
)
But MySQL doesn't support LIMIT inside subqueries, besides, I want to get
only postids from threads that are not hidden (t.visible=1 and t.open=1).
How can I do this?
Thank you for all suggestion, tips, solutions... I'm still struggling with
mysql query optimization...
Anze
Navigation:
[Reply to this message]
|