MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimized Indexing for a Schedule Table
Posted by: Wes Thompson
Date: November 25, 2010 10:47PM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
3762
November 23, 2010 11:58PM
Re: Optimized Indexing for a Schedule Table
1848
November 25, 2010 10:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.