Re: Optimized Indexing for a Schedule Table
Full table definition, table status, query explain, and example of the problem is below.
mysql> show create table trigs\G
*************************** 1. row ***************************
Table: trigs
Create Table: CREATE TABLE `trigs` (
`id` int(11) NOT NULL auto_increment,
`state` enum('OFF','ARMED','FIRED','EXPIRED') collate utf8_bin NOT NULL default 'OFF',
`prio` smallint(6) NOT NULL default '32767',
`fire_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `ix_state_fire_time` (`state`,`fire_time`),
KEY `ix_prio_fire_time` (`prio`,`fire_time`),
KEY `ix_prio` (`prio`),
KEY `ix_fire_time` (`fire_time`),
KEY `ix_state_fire_time_prio` (`state`,`prio`,`fire_time`)
) ENGINE=InnoDB AUTO_INCREMENT=3627241 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
mysql> show table status\G
*************************** 1. row ***************************
Name: trigs
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3056337
Avg_row_length: 36
Data_length: 110755840
Max_data_length: 0
Index_length: 311508992
Data_free: 0
Auto_increment: 3627241
Create_time: 2010-11-26 01:17:35
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: InnoDB free: 1043456 kB
1 row in set (0.55 sec)
mysql> explain select * from trigs where state='ARMED' and fire_time < 20101121 order by prio,fire_time limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trigs
type: ref
possible_keys: ix_state_fire_time,ix_fire_time,ix_state_fire_time_prio
key: ix_state_fire_time_prio
key_len: 1
ref: const
rows: 169790
Extra: Using where
1 row in set (0.00 sec)
mysql> select * from trigs where state='ARMED' and fire_time < 20101127 order by prio,fire_time limit 10;
+--------+-------+------+---------------------+
| id | state | prio | fire_time |
+--------+-------+------+---------------------+
| 571555 | ARMED | 0 | 2010-11-26 01:08:41 |
| 571556 | ARMED | 0 | 2010-11-26 01:08:41 |
| 571557 | ARMED | 0 | 2010-11-26 01:08:42 |
| 571558 | ARMED | 0 | 2010-11-26 01:08:43 |
| 571559 | ARMED | 0 | 2010-11-26 01:08:45 |
| 571560 | ARMED | 0 | 2010-11-26 01:08:46 |
| 571561 | ARMED | 0 | 2010-11-26 01:08:49 |
| 571562 | ARMED | 1 | 2010-11-26 08:15:43 |
| 571563 | ARMED | 1 | 2010-11-26 08:15:44 |
| 571564 | ARMED | 1 | 2010-11-26 08:15:45 |
+--------+-------+------+---------------------+
10 rows in set (0.04 sec)
mysql> select * from trigs where state='ARMED' and fire_time < 20101126 order by prio,fire_time limit 10;
+--------+-------+------+---------------------+
| id | state | prio | fire_time |
+--------+-------+------+---------------------+
| 804454 | ARMED | 902 | 2010-11-25 09:56:09 |
| 804455 | ARMED | 902 | 2010-11-25 09:56:09 |
| 804456 | ARMED | 902 | 2010-11-25 09:56:10 |
| 804457 | ARMED | 902 | 2010-11-25 09:56:10 |
| 804458 | ARMED | 902 | 2010-11-25 09:56:10 |
| 804459 | ARMED | 902 | 2010-11-25 09:56:10 |
| 804460 | ARMED | 902 | 2010-11-25 09:56:11 |
| 804461 | ARMED | 902 | 2010-11-25 09:56:11 |
| 804462 | ARMED | 902 | 2010-11-25 09:56:12 |
| 804463 | ARMED | 902 | 2010-11-25 09:56:12 |
+--------+-------+------+---------------------+
10 rows in set (2.89 sec)
mysql> select * from trigs where state='ARMED' and fire_time < 20101121 order by prio,fire_time limit 10;
+---------+-------+------+---------------------+
| id | state | prio | fire_time |
+---------+-------+------+---------------------+
| 1354693 | ARMED | 908 | 2010-09-26 14:13:12 |
| 1354694 | ARMED | 908 | 2010-09-26 14:13:12 |
| 1354695 | ARMED | 908 | 2010-09-26 14:13:12 |
| 1354696 | ARMED | 908 | 2010-09-26 14:13:12 |
| 1354697 | ARMED | 908 | 2010-09-26 14:13:12 |
| 1354698 | ARMED | 908 | 2010-09-26 14:13:12 |
| 1354699 | ARMED | 908 | 2010-09-26 14:13:12 |
| 1354700 | ARMED | 908 | 2010-09-26 14:13:12 |
| 1354701 | ARMED | 908 | 2010-09-26 14:13:13 |
| 1354702 | ARMED | 908 | 2010-09-26 14:13:13 |
+---------+-------+------+---------------------+
10 rows in set (7.27 sec)