Details
Major Description
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?