(This thread is kind of similar to the query optimization thread going, but I don't want to hijack that thread.)
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:
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
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.
SELECT bidders.keyword FROM bidders right JOIN keywords on bidders.keyword = keywords.keyword where adv_url = <argument>
This runs at almost the exact same speed (about .5 seconds faster). Keyword is indexed in both the Keywords and Bidders table. If I run an EXPLAIN on the above, simplified statement, I get
+----------+-------+---------------+---------+---------+------------------+--------+-------------+
| 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 |
+----------+-------+---------------+---------+---------+------------------+--------+-------------+
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?
