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