| 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 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.
Description: I have a table having about 10M records, and using int unsigned `id` as primary key. When I try to UPDATE a record by using `id` and providing an out-of-ranged but looks-like-number value, the sql statement will run very long time and it looks like causing a full table scan, which will be a really serious performance issue, and from the DB server side, I cannot find any workaround. This dose not affect SELECT stat. I have both MyISAM and InnoDB version of this table and the problem is totally same. I have reproduced this bug on 5.0.51a, 5.0.77 and 5.1.34 . I'm not sure this is a parser or optimizer bug, and just randomly choose one as the bug category. How to repeat: The example presented here is not the same one above, but I think is necessary for demonstrate the bug. the table: mysql> show create table past30; +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | past30 | CREATE TABLE `past30` ( `vid` int(10) unsigned NOT NULL, `date` timestamp NOT NULL default '0000-00-00 00:00:00', `count` int(10) unsigned NOT NULL, PRIMARY KEY (`vid`,`date`), KEY `date` (`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show table status like "past30"\G *************************** 1. row *************************** Name: past30 Engine: InnoDB Version: 10 Row_format: Compact Rows: 34197504 Avg_row_length: 59 Data_length: 2044723200 Max_data_length: 0 Index_length: 562036736 Data_free: 0 Auto_increment: NULL Create_time: 2009-06-08 14:46:12 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: InnoDB free: 5120 kB 1 row in set (0.12 sec) the problem: mysql> update past30 set count=100 where vid=-1; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> update past30 set count=100 where vid='-1'; Query OK, 0 rows affected (2 min 9.28 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> update past30 set count=100 where vid='-1'+0; Query OK, 0 rows affected (0.00 sec) mysql> update past30 set count=100 where vid='aaaabbbb'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 and for SELECT: mysql> select * from past30 where vid=-1; Empty set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------+ | Warning | 1264 | Out of range value adjusted for column 'vid' at row 1 | +---------+------+-------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from past30 where vid='-1'; Empty set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------+ | Warning | 1264 | Out of range value adjusted for column 'vid' at row 1 | +---------+------+-------------------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: Using the same checking mechanism for SELECT ?