I have an analytics table that stores metrics aggregated the day level. I need to ask questions such as "For a given date range and client, what are the most popular items."

The schema is as follows:

    `imps` bigint(20) DEFAULT NULL,
    `n_id` int(11) NOT NULL,
    `b_id` bigint(20) NOT NULL,
    `date` date NOT NULL,
    `m1` bigint(20) NOT NULL,
    `m2` bigint(20) NOT NULL,
    `is_b` tinyint(1) NOT NULL,
    `i_id` varchar(128) NOT NULL,
    KEY `idx1` (`b_id`,`date`,`i_id`,`n_id`)

(This table is split into 256 partitions by HASH(b_id), in case that matters)

A representative query:

SELECT i_id, n_id, SUM(m1), SUM(m2)
WHERE b_id=1234 AND date>='2013-06-01' AND date<='2013-08-31'
GROUP BY i_id, n_id

And the EXPLAIN output:

| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                                        |
|  1 | SIMPLE      | t1    | ref  | idx1          | idx1 | 8       | const | 1395772 | Using where; Using temporary; Using filesort |

On a table with ~110M records these queries take > 10s to execute. I believe the SUM function in the ORDER BY clause forces the entire resultset satisfied by the WHERE clause to be written to disk, hence the "Using temporary; Using filesort" messages.

Do I have any recourse in improving the performance of these queries short of redesigning the schema and application?

Edit: Here is a SQLFiddle with the schema and dummy data: http://sqlfiddle.com/#!2/beb63/1

  • First can you run an EXPLAIN PARTITIONS and post it here? to see if the MySQL is able to use partition pruning? because the problem is "Using temporary; Using filesort" this slows down MySQL very bad... because it could be making an temporary disk based MyISAM table that needs to be sorted on disk if the memory cant hold the result.. and this is ORDER BY (SUM(m1) + SUM(m2)) DESC is causing "Using temporary; Using filesort" Sep 27 '13 at 19:37
  • @RaymondNijland Only a single partition is listed in the EXPLAIN PARTITIONS output. This is expected, since I'm partitioning against b_id which is used in the WHERE clause. Sep 27 '13 at 19:44
  • 1
    Yes thats right.. Can you maybe give some example data on sqlfriddle and paste the link here with expected result? The problem is you dont have m1 and m2 indexed (note you cant fix this by indexing m1 and m2 also) but the GROUP BY uses ASC and ORDER BY desc (this always triggers "Using temporary; Using filesort" in the explain because the CPU need the resort the result again and again to match the ordering) and calculation thats why "Using temporary; Using filesort" is needed you cant avoid this with this kind of query. So the best option is to use an divide and conquer strategy Sep 27 '13 at 20:19
  • ... or an inner self join that uses an derived Table calculate (SUM(m1) + SUM(m2)) and inner join that id's back with the outer. but still it should use an temporary table Sep 27 '13 at 20:26

Did you tried in the following way:

SELECT i_id, n_id, SUM(m1) AS sum1, SUM(m2) sum2
WHERE b_id=1234 AND date>='2013-06-01' AND date<='2013-08-31'
GROUP BY i_id, n_id
ORDER BY sum1 + sum2 DESC

I'm in SQL Server and there it's helping me.

  • 2
    The LIMIT 10 in the subquery, without an ORDER BY would result in choosing the 10 result rows in a arbitrary way - and not how the OP wants. Sep 27 '13 at 20:09
  • 1
    In short: This will be faster but will give different results. Sep 27 '13 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.