MySQL Forums
Forum List  »  Performance

Re: Help needed on tuning query with join
Posted by: N.Z. Bear
Date: October 12, 2004 06:19PM

I think I've found what might be the problem: it actually seems to be the 'order by' clause (which I omitted from the original post cause I didn't think it was significant).

What seems to happen is that with the order by clause, the DB has to use a temporary table. That in turn ends up getting copyed to disk --- which I presume is what's killing my performance. When I do a processlist, I see:

| 635181 | nzbear_ttlb | localhost | nzbear_wmdi | Query | 1810 | Copying to tmp table on disk | SELECT a.dc_title,a.dc_identifier,a.current_rank,a.id,b.source_id,b.destid,b.dest_url from wmdi_webl |

Here's the EXPLAIN of the actual query (I tried 'right join', 'left' seems to behave the same):

EXPLAIN SELECT a.dc_title, a.dc_identifier, a.current_rank, a.id, b.source_id, b.destid, b.dest_url
FROM wmdi_weblogs a
RIGHT JOIN links b ON a.id = b.source_id
WHERE (
b.destid = '94'
)
AND (
b.source_id != '94'
)
AND (
(
b.blacklist IS NULL
)
OR (
b.blacklist <> 'Y'
)
)
ORDER BY a.current_rank DESC

table type possible_keys key key_len ref rows Extra
b ref destid destid 3 const 69 Using where; Using temporary; Using filesort
a eq_ref PRIMARY PRIMARY 4 b.source_id 1


...and here's the EXPLAIN of the query with the ORDER BY Clause removed:

SQL-query:
EXPLAIN SELECT a.dc_title, a.dc_identifier, a.current_rank, a.id, b.source_id, b.destid, b.dest_url
FROM wmdi_weblogs a
RIGHT JOIN links b ON a.id = b.source_id
WHERE (
b.destid = '94'
)
AND (
b.source_id != '94'
)
AND (
(
b.blacklist IS NULL
)
OR (
b.blacklist <> 'Y'
)
)


table type possible_keys key key_len ref rows Extra
b ref destid destid 3 const 69 Using where
a eq_ref PRIMARY PRIMARY 4 b.source_id 1

---------------

is there a better way to get the results sorted than the 'order by'?

Thanks for the help...

-NZB

Options: ReplyQuote


Subject
Views
Written By
Posted
6256
October 12, 2004 02:42PM
Re: Help needed on tuning query with join
3066
October 12, 2004 06:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.