Deathly Slow Query

[Anyaer]

I've been tasked with finding out why a certain page in our system is loading very slowly. After doing a few things, cleaning up some HTML and whatnot it loaded a couple seconds faster. But I finally found that most of the slowdown is due to a single MySQL Query:

[code]SELECT distinct bidders.keyword, bidders.adv_url, bidders.bid,
keywords.searches FROM bidders right JOIN keywords on bidders.keyword = keywords.keyword where adv_url = <argument> group by bidders.keyword[/code]

So I'm basically after getting keywords.searches into the bidders results. The query takes about 7 seconds to execute and returns 173 rows. Keywords is a fairly large table, about 200,000. Bidders is similar. First thing I did was try to pair down the statement just to see if that'd help.

[code]SELECT bidders.keyword FROM bidders right JOIN keywords on bidders.keyword = keywords.keyword where adv_url = <argument>[/code]

This runs at almost the exact same speed (about .5 seconds faster). Keyword is indexed in both the Keywords and Bidders table and adv_url is indexed in Bidders (it doesn't exist in Keywords). If I run an EXPLAIN on the above, simplified statement, I get

[code]+----------+-------+---------------+---------+---------+------------------+--------+-------------+
| table    | type  | possible_keys | key    | key_len | ref              | rows  | Extra      |
+----------+-------+---------------+---------+---------+------------------+--------+-------------+
| keywords | index | [NULL]        | keyword |    100 | [NULL]          | 132973 | Using index |
| bidders  | ref  | keyword      | keyword |    100 | keywords.keyword |    13 | where used  |
+----------+-------+---------------+---------+---------+------------------+--------+-------------+[/code]

I've also run analysis and optimization on both tables involved. At this point, I'm not sure if there's something I can do with this or if this is a fundamental problem of the table design. Is there some other way I can run this query and achieve the same effect but in another way, or optimize this?

[shoz]

If there are only 13 rows in the bidders table that match the adv_url, then it should be the first table that MYSQL looks at.

Is there a specific reason you're using a "right join"? If the WHERE clause limits the result based on the bidders table then you should be using a LEFT JOIN in that query. Even if that were not the case I would have recommended using a LEFT JOIN but you'd change the order that you reference the tables in the query.

If after changing the RIGHT JOIN to a LEFT JOIN you still don't get the desired results post the version of mysql you're using
[code]
SELECT VERSION()
[/code]

The CREATE STATEMENT for both tables
[code]
SHOW CREATE TABLE tablename
[/code]

and the EXPLAIN for the new query. Btw, If the objective is to get kerwords.searches in the result set then it would be best to put that in the column list as well while testing.

Note that you can force the issue by using a subquery to go through the bidders table before doing the join but there should be no need to.

[shoz]

I should also mention that If the purpose of the query is to only retrieve info on keywords that have a match in both tables then an INNER JOIN would be more appropriate.

[Anyaer]

And bang, that did it. The result sets aren't all that different: the left join means that some keyword.searches come up as NULL, but it's easy to screen for that because I have to iterate through the result set anyway (the whole point of the call is to get keyword.searches and then add them all up; so during the foreach I just say

[code]if(!isset($row['searches'])) {
    continue;
}[/code]

However, the query with a left join takes .07 seconds while the right join takes about 6.5. Maybe my follow-up question is, why such a horrendous difference in the query times? As I understand it, the difference between Left and Right joins is that left returns a result for every row in the left table even if it doesn't have a match (in this case, it would return every bidders.keyword even if it doesn't have a matching keywords.keyword), whereas a right join returns rows only where bidders.keyword = keywords.keyword. So in a left join on this example, the way that would manifest are rows where keywords.searches = NULL. If I understand it correctly, I don't see why the right join should take 6 seconds longer to execute.

[shoz]

[quote author=Anyaer link=topic=123990.msg513220#msg513220 date=1169739751]
And bang, that did it. The result sets aren't all that different: the left join means that some keyword.searches come up as NULL, but it's easy to screen for that because I have to iterate through the result set anyway (the whole point of the call is to get keyword.searches and then add them all up; so during the foreach I just say

[code]if(!isset($row['searches'])) {
    continue;
}[/code]
[/quote]

Change the LEFT JOIN to an INNER JOIN to retrieve a result where the keyword is in both tables. It's also likely that you can do the sum in MYSQL. Explain how you're summing the results if you'd like help on that.

[quote=Anyaer]
However, the query with a left join takes .07 seconds while the right join takes about 6.5. Maybe my follow-up question is, why such a horrendous difference in the query times? As I understand it, the difference between Left and Right joins is that left returns a result for every row in the left table even if it doesn't have a match (in this case, it would return every bidders.keyword even if it doesn't have a matching keywords.keyword), whereas a right join returns rows only where bidders.keyword = keywords.keyword. So in a left join on this example, the way that would manifest are rows where keywords.searches = NULL. If I understand it correctly, I don't see why the right join should take 6 seconds longer to execute.
[/quote]

A RIGHT JOIN does the opposite of a LEFT JOIN. ie instead of returning rows in the left table even if there are no matching rows in the right table it returns rows in the right table even if there are no matching rows in the left table.

An INNER JOIN would only return rows where bidders.keyword=keywords.keyword. The only reason the RIGHT JOIN appears to be giving you that result is because the WHERE clause limits the result set to only those rows where the bidders.adv_url=$url. Which would mean that rows that are given NULL because of the RIGHT JOIN wouldn't be in the end result.

The general idea as far as why the LEFT JOIN is faster than the RIGHT JOIN in this instance is because as can be seen from the EXPLAIN output, MYSQL does a full table scan of the keywords table before doing the JOIN and when you do the LEFT JOIN you should see that MYSQL first looks only at the small number of rows in the bidders table that match the WHERE clause  and then uses that small number of keywords to JOIN the keywords table.

You can read this article on [url=http://hackmysql.com/optimize]Optimizing MYSQL queries[/url] for more info.

[Anyaer]

Understood.