Optimized Indexing for a Schedule Table
I have a pretty simple table that contains a schedule. I am having difficulty determining the indexing that is appropriate for this table. My table setup and problem description follow below.
Any help is appreciated. Thanks to anyone that can help.
I have a InnoDb table with 3 million rows and the following columns
id primary key, foreign key
fire_time datetime not null
priority smallint not null
state enum('OFF','ARMED','FIRED','EXPIRED') not null
I need to search for the condition
fire_time < now() and state='ARMED'
And I need the rows ordered by
priority
fire_time
I currently have the following BTREE index, all ascending
(priority,fire_time,state)
The results of the described setup give very fast searches when there are rows that meet the search criteria and have low values of priority. But, when the only matching rows have high values of priority, the search is slow. I figure the query is traversing down each leg of the BTREE for the index (priority,fire_time,state) in order of priority and takes a while to get to the larger values of priority.
EXPLAIN on a SELECT query gives
id: 1
select_type: SIMPLE
table: trigs
type: range
possible_keys: index_priority_fire_time_state
key: index_priority_fire_time_state
key_len: 2
ref: NULL
rows: 1587414
Extra: Using where
Is there a better indexing solution?
Subject
Views
Written By
Posted
Optimized Indexing for a Schedule Table
3847
November 23, 2010 11:58PM
1280
November 25, 2010 02:35PM
1909
November 25, 2010 10:47PM
1347
November 25, 2010 11:05PM
1789
November 26, 2010 01:42PM
1416
November 26, 2010 11:36PM
1513
November 27, 2010 01:31PM
1415
November 27, 2010 02:44PM
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.