|
|
Posted by Good Man on 12/06/07 16:44
Anze <anzenews@volja.net> wrote in
news:OzS5j.2460$HS3.78862@news.siol.net:
> 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;
> -----
<snip>
> How can I do this?
>
> Thank you for all suggestion, tips, solutions... I'm still struggling
> with mysql query optimization...
>
> Anze
>
>
You definetly should have an index on t.visible and t.open, thats for
sure. My guess is that you don't. My rule-of-thumb (mine, not "the")
is to put an INDEX on every column that will be involved in a JOIN or
WHERE clause:
Anyways, put INDEXes on those columns and use a nice proper JOIN:
SELECT
p.postid, t.title 'ttitle', p.title 'ptitle', p.pagetext 'text'
FROM
thread t
JOIN post p ON t.threadid=p.threadid
WHERE t.visible=1 AND t.open=1
ORDER BY
p.potid DESC
LIMIT 5
Navigation:
[Reply to this message]
|