MySQL join two large tables is very slow

Question

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

The structure of 'history' table

The structure of 'cache' table

Answer 1

It is generally a good idea to index columns that participate in JOIN predicates or WHERE clauses. A common mistake is to create several one column indexes instead of fewer multi-column indexes. Here we can benefit from both url and time in history (look at all queries against these tables and you may find that you can add additional columns to these indexes):

CREATE INDEX x01_history_url ON HISTORY (URL, TIME);
CREATE INDEX x01_cache_url ON CACHE (URL);

Second, try to unnest the queries. MySQL has limitations on what kind of query rewrites it is able to do, so the nesting may cause unnecessary overhead.

SELECT COUNT(th.url) as total, tc.url, tc.json 
FROM history th
JOIN cache tc
    ON th.url = tc.url
WHERE th.time > UNIX_TIMESTAMP()-3600
GROUP BY tc.url, tc.json
ORDER BY COUNT(th.url) DESC 
LIMIT 30

Note that this query is semantically different from your query so you might get a different result. If that is a problem you may want to keep the LIMIT 30 construction in a subquery as before. You may also consider if you can add a similar limit to CACHE, is there an upper bound on how many CACHE rows you must investigate to get 30 rows in total?

INNER JOIN (SELECT cache.url, cache.json 
            FROM cache
            ORDER BY ? LIMIT ?) tc

Answer 2

For the first query:

SELECT COUNT(*) as total,  -- * is the common pattern
       url
    FROM history
    WHERE time > NOW() - INTERVAL 1 HOUR
    GROUP BY url
    ORDER BY COUNT(*) DESC 
    LIMIT 30

INDEX(time, url)  -- in this order
INDEX(url, time)  -- maybe this order

Use both indexes; different MySQL versions and different time range may use one index versus the other.

For the second query, don't unnecessarily use a derived table:

INNER JOIN (SELECT cache.url, cache.json FROM cache) tc
   ON th.url = tc.url

-->

INNER JOIN cache tc
    ON th.url = tc.url

And cache needs INDEX(url).

Are the two tables 1:1 or 1:many or many:1 or many:many? It may make a difference for the outer GROUP/ORDER/LIMIT.

While you are at it, please provide SHOW CREATE TABLE for the tables.

Answer 3

try this:

SELECT * FROM (
  SELECT COUNT(history.url) as total, history.url
  FROM history
  WHERE history.time > UNIX_TIMESTAMP()-3600
  GROUP BY history.url
  ) tmp
ORDER BY total DESC 
LIMIT 30