|
|
Posted by Captain Paralytic on 12/06/07 15:32
On 6 Dec, 13:13, Anze <anzen...@volja.net> wrote:
> 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
Since there is no PHP in this, I would suggest that you would most
likely get more response in comp.databases.mysql, which also tends to
be a far busier group.
Navigation:
[Reply to this message]
|