Take the 2-minute 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.

I think I've got a very simple model and query, but I cannot find a way to make use of indexes to boost performance.

I wonder if there is a trick in my model, or MySQL query, to speed up the execution time to get an overview of my population at a specific time.

I have the following table which holds the status (history) of my users:

CREATE TABLE `status` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` varchar(9) NOT NULL,
 `status` enum('NEW','REGISTERED','CANCELED') NOT NULL,
 `timestamp` datetime NOT NULL,
 `explanation` varchar(25) NOT NULL,

 PRIMARY KEY (`id`),
 KEY `index1` (`user_id`,`timestamp`,`id`),
 KEY `index2` (`timestamp`,`user_id`,`id`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8

My table will hold a lot of users (5 million), which can have one, or more statuses (so the table will probably hold 10+ million records). Records are inserted in chronological order, so ID and timestamp are "in sync" (timestamps are not required to be unique, but the autoincrement ID's are)

What I do to get an overview of my population at a specific time is:

SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;

Which gives me the correct ID's, but since MySQL cannot use indexes when sorting on a column (for the where clause), which is not in the GROUP BY, there is no performance.

The explain tells me:

explain SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;
+----+-------------+--------+-------+---------------------+---------------------+---------+------+----------+-----------------------------------------------------------+
| id | select_type | table  | type  | possible_keys       | key                 | key_len | ref  | rows     | Extra                                                     |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | status | range | index2              | index2              | 8       | NULL | 13897517 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------------+---------------------+---------+------+----------+-----------------------------------------------------------+

Does anyone knows how I can optimize my model, or my query (for instance with subqueries) (, to allow MySQL to make use of indexes), and so boost performance?

What I eventually want is:

SELECT user_id, status, timestamp, explanation FROM `status` WHERE id IN (
  SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id
);
share|improve this question
1  
Can you add the EXPLAIN plan in the question? –  ypercube Sep 16 '13 at 12:34
    
Do you have any timestamp values that are in the future? The WHERE timestamp <= NOW() sounds redundant otherwise. –  ypercube Sep 16 '13 at 12:39
1  
I indeed have timestamps in the future, and sometimes am interested in overviews in the past (so NOW() isn't constant, but useful for this example). I will add the explain to my post right now... –  LvanderRee Sep 16 '13 at 12:50
    
How much time does the query takes to run? (the first one, not the one with the IN subquery) –  ypercube Sep 16 '13 at 14:01
    
I have a test-set with 17 million distinct user-ids and 28 million statuses, running the inner-query then takes: 17000000 rows in set (6 min 35.81 sec) –  LvanderRee Sep 16 '13 at 14:29
add comment

2 Answers

up vote 2 down vote accepted

Ok, i tried to reproduse your case:

mysql> insert into status values (1,'a','NEW',NOW(),'test'); Query OK, 1 row affected (0.00 sec)

mysql> insert into status values (2,'b','NEW',NOW(),'test2'); Query OK, 1 row affected (0.00 sec)

mysql> insert into status values (3,'c','NEW',NOW(),'test3'); Query OK, 1 row affected (0.00 sec)

mysql> explain  SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;
+----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key    | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+
|  1 | SIMPLE      | status | index | index2        | index1 | 41      | NULL |    3 | Using where; Using index |
+----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+

Mysql uses index like you wanted.

And then:

mysql> alter table status drop index index1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain  SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;
+----+-------------+--------+-------+---------------+--------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table  | type  | possible_keys | key    | key_len | ref  | rows | Extra                                                     |
+----+-------------+--------+-------+---------------+--------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | status | index | index2        | index2 | 41      | NULL |    3 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+--------+---------+------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

The plan is like yours. Please ensure that index1 is really present.

I am runnig mysql 5.5.33-31.1-log You can even try 5.6.

Also please try to force index1 and post the results.

share|improve this answer
    
Also you don't need to add 'id' column in the end of the index, since secondary indexes naturally contain PK value in InnoDb. –  netneladno Sep 16 '13 at 16:34
    
Thanks @netneladno Forcing index1 did the trick! –  LvanderRee Sep 17 '13 at 7:42
add comment

Thanks to netneladno I have the solution:

I have to force the index (although I don't understand exactly why).

Without the forced index I'm not getting the desired result, as shown before:

mysql> explain SELECT MAX(id) FROM `status` WHERE timestamp <= NOW() GROUP BY user_id;
+----+-------------+--------+-------+----------------------------+--------+---------+------+----------+-----------------------------------------------------------+
| id | select_type | table  | type  | possible_keys              | key    | key_len | ref  | rows     | Extra                                                     |
+----+-------------+--------+-------+----------------------------+--------+---------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | status | range | index2                     | index2 | 8       | NULL | 13897517 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+----------------------------+--------+---------+------+----------+-----------------------------------------------------------+

I guess because of the immense amount of rows.

However when I force the index I get the following result:

mysql> explain SELECT MAX(id) FROM `status` FORCE INDEX (index1) WHERE timestamp <= NOW() GROUP BY user_id;
+----+-------------+--------+-------+---------------+--------+---------+------+----------+--------------------------+
| id | select_type | table  | type  | possible_keys | key    | key_len | ref  | rows     | Extra                    |
+----+-------------+--------+-------+---------------+--------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | status | index | NULL          | index1 | 19      | NULL | 27795034 | Using where; Using index |
+----+-------------+--------+-------+---------------+--------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

And the actual row-ids now present themselves in:

17000000 rows in set (11.06 sec)

With a join I can get the complete rows in a minute:

SELECT s.* from status s join (SELECT MAX(id) as max_id FROM `status` FORCE INDEX (index1) WHERE timestamp <= NOW() GROUP BY user_id) rows on (s.id = rows.max_id);

17000000 rows in set (1 min 17.61 sec)

and with an additional group-by I can also get an overview in a minute:

SELECT s.status, count(*) from status s join (SELECT MAX(id) as max_id FROM `status` FORCE INDEX (index1) WHERE timestamp <= NOW() GROUP BY user_id) rows on (s.id = rows.max_id) group by s.status;
+------------------+----------+
| status           | count(*) |
+------------------+----------+
| NEW              | 11320852 |
| REGISTERED       |   568960 |
| CANCELED         |  5110188 |
+------------------+----------+

3 rows in set (1 min 11.03 sec)

share|improve this answer
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.