Explore our sites

Stack Exchange
tour help
Take the tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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: 

CREATE TABLE `t1` (
    `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(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)
FROM t1
WHERE b_id=1234 AND date>='2013-06-01' AND date<='2013-08-31'
GROUP BY i_id, n_id
ORDER BY (SUM(m1) + SUM(m2)) DESC
LIMIT 10 OFFSET 0

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

share|improve this question
 
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" –  Raymond Nijland Sep 27 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. –  Rich Schumacher Sep 27 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 –  Raymond Nijland Sep 27 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 –  Raymond Nijland Sep 27 at 20:26
add comment

1 Answer

up vote -1 down vote

Did you tried in the following way:

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

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

share|improve this answer
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. –  ypercube Sep 27 at 20:09
1  
In short: This will be faster but will give different results. –  ypercube Sep 27 at 20:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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