Good day!

I found problem and strange behavior of update

there is table

CREATE TABLE `schedule_test` (

   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

   `datetime_processed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

   `datetime_next` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

   `sign` bigint(20) unsigned NOT NULL DEFAULT '0',

   `lock` tinyint(4) NOT NULL DEFAULT '0',

   PRIMARY KEY (`id`),

   KEY `idx_lock_dt_nxt` (`lock`,`datetime_next`)

) ENGINE=InnoDB;

it's a simple task scheduler and table has near 45k rows

Task selecting doing in this way:

update schedule_test as sa 

set 

     sa.`lock` = 1, 

     sa.sign = 2123123123,

     sa.`datetime_processed` = now()

where 

       sa.`lock` = 0 

and sa.`datetime_next` <= now()

order by sa.`datetime_next`  asc

limit 10;

problem is that update doesnt use index `idx_lock_dt_nxt` for selecting and mysql does table fullscan (read all 45k).
I look in innotop count readed rows.

But if rewrite update to select, it works as should - use undex and innodb read from table only 10 rows.

select sa.`lock`, sign, datetime_next

from  schedule_test as sa 

where 

       sa.`lock` = 0 

and sa.`datetime_next` <= now()

order by sa.`datetime_next`  asc

limit 10;

explain of this:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra

1    SIMPLE    sa    ref    lock    lock    1    const    22716    Using index condition; Using where

for me is not understandable nature this behaviour. as i know select and update should use the same execution plan.

As an experiment, i modify index and make it so KEY `idx_lock_dt_nxt` (`datetime_next`)

in this case update begins using it for select rows by date, but i keep in my mind, it works like full scan but little softer, anyway.
because after some time all rows with minimal datetime will be locked (lock=1) and mysql have to scan more and more rows for checking where condition.

i think may be there is an error in choosing good query plan execution for update?