Status: Closed (View Workflow)
Affects Version/s: 10.0.1, 5.5.30, 5.3.12
version_comment MariaDB Server
I found problem and strange behavior of update
there is table
it's a simple task scheduler and table has near 45k rows
Task selecting doing in this way:
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.
explain of this:
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?