Why is this query being logged as "not using indexes"?

Question

For some reason my slow query log is reporting the following query as "not using indexes" and for the life of me I cannot understand why.

Here is the query:

update  scheduletask
set     active = 0
where   nextrun < date_sub( now(), interval 2 minute )
and     enabled = 1
and     active = 1;

Here is the table:

CREATE TABLE `scheduletask` (
  `scheduletaskid` int(11) NOT NULL AUTO_INCREMENT,
  `schedulethreadid` int(11) NOT NULL,
  `taskname` varchar(50) NOT NULL,
  `taskpath` varchar(100) NOT NULL,
  `tasknote` text,
  `recur` int(11) NOT NULL,
  `taskinterval` int(11) NOT NULL,
  `lastrunstart` datetime NOT NULL,
  `lastruncomplete` datetime NOT NULL,
  `nextrun` datetime NOT NULL,
  `active` int(11) NOT NULL,
  `enabled` int(11) NOT NULL,
  `creatorid` int(11) NOT NULL,
  `editorid` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `edited` datetime NOT NULL,
  PRIMARY KEY (`scheduletaskid`),
  UNIQUE KEY `Name` (`taskname`),
  KEY `IDX_NEXTRUN` (`nextrun`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1;

Answer 1

Add another index like this

KEY `IDX_COMB` (`nextrun`, `enabled`, `active`)

I'm not sure how many rows your table have but the following might apply as well

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.)

Answer 2

try using the "explain" command in mysql. http://dev.mysql.com/doc/refman/5.5/en/explain.html I think explain only works on select statements, try:

explain select * from scheduletask where nextrun < date_sub( now(), interval 2 minute ) and enabled = 1 and active = 1;

Maybe if you use, nextrun = ..., it will macht the key IDX_NEXTRUN. In your where clause has to be one of your keys, scheduletaskid, taskname or nextrun

Answer 3

Sorry for the short answer but I don't have time to write a complete solution.

I believe you can fix your issue by saving date_sub( now(), interval 2 minute ) in a temporary variable before using it in the query, see here maybe: MySql How to set a local variable in an update statement (Syntax?).