|
|
Posted by Tom on 12/07/07 19:31
On Thu, 06 Dec 2007 10:44:19 -0600, Good Man wrote...
>
>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
>
The biggest culprit I always run into is indexing. Sometimes tables may change
and columns get added and after a few "explain analyze" queries I can usually
find them and add an index to speed things up considerably. Won't solve all
performance issues, but does help a lot.
Tom
--
NewsGuy Accounts Go Jumbo!
NewsGuy Express increased from 30 to 50 GB of download capacity
http://newsguy.com/overview.htm
Navigation:
[Reply to this message]
|