How do you group, bucket, and filter a result sets for a very large dataset

Question

Sorry, the question title is somewhat vague, so here's a working example.

I have a table into which each user (userid) gets a value every few days. I want to find the last of those values for each user, broken out by month, and count their number into a range.

Here's an example table and representative data:

CREATE TABLE `datasource` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `userId` INT UNSIGNED NOT NULL ,
    `unixts` INT UNSIGNED NOT NULL ,
    `value` INT UNSIGNED NOT NULL ,
    INDEX ( `userId` )
);

INSERT INTO `datasource` 
    (`userId`, `unixts`, `value`)
VALUES 
    (1, UNIX_TIMESTAMP('2010-07-01'), 500),
    (1, UNIX_TIMESTAMP('2010-07-15'), 610),
    (1, UNIX_TIMESTAMP('2010-08-02'), 740),

    (2, UNIX_TIMESTAMP('2010-07-03'), 506),
    (2, UNIX_TIMESTAMP('2010-07-18'), 640),
    (2, UNIX_TIMESTAMP('2010-08-09'), 340),

    (3, UNIX_TIMESTAMP('2010-07-03'), 506),
    (3, UNIX_TIMESTAMP('2010-08-18'), 640)
;

Now, here's a query to get what I am after:

select
    month(FROM_UNIXTIME(unixts)) as month,
    sum( if( value >= 700, 1, 0) ) as '700 and up',
    sum( if( value BETWEEN 600 AND 699, 1, 0) ) as '600-699',
    sum( if( value BETWEEN 500 AND 599, 1, 0) ) as '500-599',
    sum( if( value <= 499, 1, 0) ) as '499 and below',
    count(*) as total
from
    datasource
where
    id in (
        select 
            max(id)
        from 
            datasource 
        where 
            unixts between UNIX_TIMESTAMP('2010-07-01') and UNIX_TIMESTAMP('2010-09-01')
        group by 
            userId, month(from_unixtime(unixts))
    )
group by
    month(FROM_UNIXTIME(unixts));

+-------+------------+---------+---------+---------------+-------+
| month | 700 and up | 600-699 | 500-599 | 499 and below | total |
+-------+------------+---------+---------+---------------+-------+
|     7 |          0 |       2 |       1 |             0 |     3 |
|     8 |          1 |       1 |       0 |             1 |     3 |
+-------+------------+---------+---------+---------------+-------+

This query works great for our small result set. However, if you toss 44 million rows into the datasource table, it grinds to a halt.

Is there an optimized way to write this query that can achieve what I want without pegging mysql down for several days?

Answer 1

Create an index on the value column.

create index value_index ON datasource(value)

You should only have to do that once. It will slow down your inserts slightly though.

Answer 2

Try EXPLAIN select...;

This will tell you how the query is working. You can then see if a full table scan is taking place for any reason and take steps to correct it. This would probably include the suggestion Cfreak is making. Alternatively, post the results here and we'll see what we can do.