I have two tables, one of them contains a history of downloaded urls, while the other table contains details about each url.
The following query groups the URLs by the number of repetitions during the past hour.
SELECT COUNT(history.url) as total, history.url
FROM history
WHERE history.time > UNIX_TIMESTAMP()-3600
GROUP BY history.url
ORDER BY COUNT(history.url) DESC
LIMIT 30
The query above takes about 800ms to execute, not fast enough, but acceptable,
however, when joining it with the cache table, the new query takes about 25s to execute, which is very slow.
SELECT th.total, th.url, tc.url, tc.json
FROM (SELECT COUNT(history.url) as total, history.url
FROM history
WHERE history.time > UNIX_TIMESTAMP()-3600
GROUP BY history.url
ORDER BY COUNT(history.url) DESC
LIMIT 30
) th
INNER JOIN (SELECT cache.url, cache.json FROM cache) tc
ON th.url = tc.url
GROUP BY th.url
ORDER BY th.total DESC
LIMIT 30
I think that this might be happening because in 'tc', the whole cache table is being loaded, and it contains 1million+ entries.
When I use the first query, and then programmatically iterate the results and then run a SELECT query from cache for each result, it is much faster. Is there anyway to speed up my second query?
P.S. I'm using InnoDB
UPDATE
The output of the second query with EXPLAIN