Bug #45424 | UPDATE statement cause full table scan ? | ||
---|---|---|---|
Submitted: | 10 Jun 2009 6:58 | Modified: | 10 Jun 2009 7:59 |
Reporter: | Lance Li | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.0.51a, 5.0.77 and 5.1.34, 5.1, 6.0 bzr | OS: | Linux |
Assigned to: | CPU Architecture: | Any | |
Tags: | full table scan, performance, regression, UPDATE |
[10 Jun 2009 6:58]
Lance Li
[10 Jun 2009 7:26]
Sveta Smirnova
Thank you for the report. Please provide output of SHOW STATUS LIKE 'Handler%'; before and after problem UPDATE statement.
[10 Jun 2009 7:40]
Lance Li
here it is. mysql> SHOW STATUS LIKE 'Handler%'; update past30 set count=100 where vid='-1'; SHOW STATUS LIKE 'Handler%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 1 | | Handler_read_key | 2 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 184780 | | Handler_rollback | 1 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 29 | +----------------------------+--------+ 15 rows in set (0.00 sec) Query OK, 0 rows affected (2 min 4.60 sec) Rows matched: 0 Changed: 0 Warnings: 0 +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | Handler_commit | 2 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 2 | | Handler_read_first | 2 | | Handler_read_key | 4 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 34984419 | | Handler_rollback | 1 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 44 | +----------------------------+----------+ 15 rows in set (0.00 sec)
[10 Jun 2009 7:59]
Sveta Smirnova
Thank you for the report. Verified as described using version 5.1 and 6.0. Version 5.0 uses table scan for both SELECT and UPDATE statements. Version 4.1 uses index.
[10 Jun 2009 8:02]
Sveta Smirnova
test case
Attachment: bug45424.test (application/octet-stream, text), 880 bytes.