Mysql is taking .7 seconds to "copy into tmp table". What is the best way to cache this kind of query?

Question

I have a group-by query that is very fast when it comes to indexing, joining, sending the results etc. Unfortunately, mysql spends 99.6% of its time "copying to tmp table" when I profile it. I am at a loss as to how I can get mysql to perform better on its own.

This group by query essentially finds the number of entities for the top 20 tags in the system. I build on this query to filter out tags that the user selects, essentially narrowing down the field. As they select a new tag, the query performance improves drastically. If they select 2 or 3 tags, the query is extremely fast in finding the number of entities with those 2 or 3 tags, plus the next most popular tags after that.

Essentially, the performance problem is when no tags are selected, or when 1 tag is selected. What is the best way to solve this problem?

1) Memory cache, like ehcache? Get hibernate involved? 2) Mysql query cache? 3) Store the 0 and 1 tag selected results for every tag in the system in a cache table, and use those for a 24-hour period? Use a job to populate the results every day?

Any other ideas? I'm using Spring, Java, Hibernate, Mysql.

Answer 1

What are the column types involved? If you look at the MySql reference you can see some of the factors that might be triggering on-disk storage for your query during processing. Is your disk system slow enough to cause this kind of issue?

Answer 2

We definitely need the query and EXPLAIN query for answering this. Possibly also with schema.

Answer 3

Is it possible for you to just create a view rather than copying files?

Answer 4

Taking a stab in the dark here. MySQL may be writing the temp table to disk if with one tag the results are bigger than the setting for in memory temp tables.

If you have the resources, you may want to consider bumping up that setting. Or you can find a way to reduce the size of the results returned for zero or 1 tag.