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?
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| 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? |
Good day! I found problem and strange behavior of update there is table {code:sql} 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; {code} it's a simple task scheduler and table has near 45k rows Task selecting doing in this way: {code:sql} 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; {code} 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. {code:sql} 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; {code} explain of this: {noformat} 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 {noformat} 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? |
| Labels | optimizer |
| Assignee | Elena Stepanova [ elenst ] |
| Labels | optimizer | optimizer upstream |
| Affects Version/s | 5.3.12 [ 12000 ] | |
| Affects Version/s | 10.0.1 [ 11400 ] |
| Fix Version/s | 10.0.3 [ 12900 ] | |
| Fix Version/s | 5.5.32 [ 13000 ] |
| Assignee | Elena Stepanova [ elenst ] | Sergei Petrunia [ psergey ] |
| Fix Version/s | 10.0.4 [ 13101 ] | |
| Fix Version/s | 10.0.3 [ 12900 ] |
| Fix Version/s | 5.5.33 [ 13300 ] | |
| Fix Version/s | 5.5.32 [ 13000 ] |
| Fix Version/s | 10.0.5 [ 13201 ] | |
| Fix Version/s | 10.0.4 [ 13101 ] |
| Rank | Ranked higher |
| Fix Version/s | 5.5.34 [ 13500 ] | |
| Fix Version/s | 5.5.33 [ 13300 ] |
| Fix Version/s | 5.5.34 [ 13700 ] | |
| Fix Version/s | 5.5.33a [ 13500 ] |
| Fix Version/s | 10.0.6 [ 13202 ] | |
| Fix Version/s | 10.0.5 [ 13201 ] |
| Fix Version/s | 5.5.35 [ 14000 ] | |
| Fix Version/s | 5.5.34 [ 13700 ] |
| Fix Version/s | 10.0.7 [ 14100 ] | |
| Fix Version/s | 10.0.6 [ 13202 ] |
| Fix Version/s | 10.0.8 [ 14200 ] | |
| Fix Version/s | 10.0.7 [ 14100 ] |
| Fix Version/s | 5.5.36 [ 14600 ] | |
| Fix Version/s | 5.5.35 [ 14000 ] |
| Fix Version/s | 10.0.9 [ 14400 ] | |
| Fix Version/s | 10.0.8 [ 14200 ] |
| Fix Version/s | 5.5.37 [ 15000 ] | |
| Fix Version/s | 5.5.36 [ 14600 ] |
| Fix Version/s | 10.0.10 [ 14500 ] |
| Fix Version/s | 5.3.13 [ 12602 ] | |
| Fix Version/s | 10.0.9 [ 14400 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | defaullt [ 27201 ] | MariaDB v2 [ 42783 ] |
| Workflow | MariaDB v2 [ 42783 ] | MariaDB v3 [ 62500 ] |
| Workflow | MariaDB v3 [ 62500 ] | MariaDB v4 [ 146616 ] |