MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimized Indexing for a Schedule Table
Posted by: Rick James
Date: November 26, 2010 01:42PM


INDEX(state, priority, fire_time);
This one should have the following characteristics:
* The query will be "Using index"
* Hopefully the ORDER BY will use the index, then filter on fire_time.
* There are often enough with the correct `state` to meet the LIMIT
* There are often enough with the priority=0 (or small number) to meet the LIMIT

What is the value of innodb_buffer_pool_size? autocommit? How much RAM do you have?

Why are there 3M rows in the table? Sounds like you have a huge backlog of things to do.

Is it always LIMIT 10?

It may be worth it to do something like
for $prio = 0, 1, ...
   SELECT ...
          where state='ARMED'
            and priority = $prio
            and fire_time < 20101127
          order by fire_time
          limit 10; 
   IF got some rows, process them and exit loop

Options: ReplyQuote

Written By
November 23, 2010 11:58PM
Re: Optimized Indexing for a Schedule Table
November 26, 2010 01:42PM

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.