MySQL Forums
Forum List  »  Optimizer & Parser

Optimized Indexing for a Schedule Table
Posted by: Wes Thompson
Date: November 23, 2010 11:58PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimized Indexing for a Schedule Table
3852
November 23, 2010 11:58PM


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.