MySQL Bugs: #51808: update .. order by .. limit 1 scans too many rows instead of single index read

Bug #51808	update .. order by .. limit 1 scans too many rows instead of single index read
Submitted:	7 Mar 2010 12:09	Modified:	17 Mar 2010 10:27
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	4.0.24,4.1.25,5.0.90,5.1.44,5.5.2	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
this is similar to bug #12915
'select .. order by .. limit 1' will only read 1 row
'update .. order by .. limit '1 reads entire table:

mysql> update t5 set c=c+1 where a<100000 order by a limit 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

| Handler_read_first         | 2     |
| Handler_read_key           | 0     |
| Handler_read_next          | 16384 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 1     |

How to repeat:
drop table if exists t5;
create table t5 (a int primary key auto_increment, b int, c int, key(b)) engine=myisam; 
insert into t5 values ();
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
insert into t5 (a) select null from t5;
update t5 set b=a, c=0;
flush status;
select * from t5 where a<100000 order by a limit 1;
show status like '%handler%';
update t5 set c=c+1 where a<100000 order by a limit 1;
show status like '%handler%';

this bug is also there when a<X has relatively small subset of the table - even if it picks up an index, it will still read all records in the range, rather than LIMIT'ing.

Verified just as described:

77-52-24-143:5.1 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 274
Server version: 5.1.45-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists t5;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t5 (a int primary key auto_increment, b int, c int, key(b)) engine=myisam; 
Query OK, 0 rows affected (0.21 sec)

mysql> insert into t5 values ();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 (a) select null from t5;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 32 rows affected (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 128 rows affected (0.01 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 256 rows affected (0.01 sec)
Records: 256  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 512 rows affected (0.02 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 1024 rows affected (0.05 sec)
Records: 1024  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 2048 rows affected (0.09 sec)
Records: 2048  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 4096 rows affected (0.21 sec)
Records: 4096  Duplicates: 0  Warnings: 0

mysql> insert into t5 (a) select null from t5;
Query OK, 8192 rows affected (0.47 sec)
Records: 8192  Duplicates: 0  Warnings: 0

mysql> update t5 set b=a, c=0;
Query OK, 16384 rows affected (0.82 sec)
Rows matched: 16384  Changed: 16384  Warnings: 0

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t5 where a<100000 order by a limit 1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    0 |
+---+------+------+
1 row in set (0.00 sec)

mysql> 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           | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
15 rows in set (0.00 sec)

mysql> update t5 set c=c+1 where a<100000 order by a limit 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 2     |
| Handler_read_key           | 0     |
| Handler_read_next          | 16384 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 1     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 1     |
| Handler_write              | 0     |
+----------------------------+-------+
15 rows in set (0.00 sec)

Looks related to bug #30584 and, thus, bug #36569. May be a duplicate.

Actually,this bug is a duplicate of the bug #36569. So, it should be marked as duplicate.

The reason why so many reads are done is exactly because filesort is forced upon in case of ORDER BY. All those reads are due to filesort calls.

Here is a stack trace that proves the point:

#0  ha_innobase::index_first (this=0xad53368, buf=0xad534b8 "¥ \206\001")
    at handler/ha_innodb.cc:4857
#1  0x083904f2 in handler::read_range_first (this=0xad53368, start_key=0x0, 
    end_key=0xa876098, eq_range_arg=false, sorted=false) at handler.cc:4229
#2  0x08390790 in handler::read_multi_range_first (this=0xad53368, 
    found_range_p=0xb4392c20, ranges=0xa876088, range_count=1, sorted=false, 
    buffer=0x0) at handler.cc:4108
#3  0x0836c1ba in QUICK_RANGE_SELECT::get_next (this=0xad53950)
    at opt_range.cc:8475
#4  0x0838c000 in find_all_keys (param=0xb4392f30, select=0xad57418, 
    sort_keys=0xb42c9020, buffpek_pointers=0xb4392da8, tempfile=0xb4392e6c, 
    indexfile=0x0) at filesort.cc:564
#5  0x0838cc64 in filesort (thd=0xad547e0, table=0xad4ecc8, 
    sortorder=0xad57520, s_length=1, select=0xad57418, max_rows=1, 
    sort_positions=true, examined_rows=0xb43931f0) at filesort.cc:243
#6  0x083168b3 in mysql_update (thd=0xad547e0, table_list=0xad56c78, 
    fields=@0xad55c18, values=@0xad55e30, conds=0xad57178, order_num=1, 
    order=0xad57300, limit=1, handle_duplicates=DUP_ERROR, ignore=false)
    at sql_update.cc:422
#7  0x0826c29f in mysql_execute_command (thd=0xad547e0) at sql_parse.cc:3055
#8  0x08272e0b in mysql_parse (thd=0xad547e0, 
    inBuf=0xad56b98 "update t5 set c=c+1 where a<100000 order by a limit 1", 
    length=53, found_semicolon=0xb4394250) at sql_parse.cc:5963
#9  0x08273aa3 in dispatch_command (command=COM_QUERY, thd=0xad547e0, 
    packet=0xad5c801 "update t5 set c=c+1 where a<100000 order by a limit 1", 
    packet_length=53) at sql_parse.cc:1224
#10 0x08274d82 in do_command (thd=0xad547e0) at sql_parse.cc:865
#11 0x08260dc5 in handle_one_connection (arg=0xad547e0) at sql_connect.cc:1127
#12 0x00be132f in start_thread () from /lib/libpthread.so.0

And filesort is indeed forced upon without any attempt at optimising it. The optimisation can not be the same as for SELECT, as the index can be updated in the command. Here is the relevant code:

   if (order && (need_sort || used_key_is_modified))
    {
      /*
	Doing an ORDER BY;  Let filesort find and sort the rows we are going
	to update
        NOTE: filesort will call table->prepare_for_position()
      */
      uint         length= 0;
      SORT_FIELD  *sortorder;
      ha_rows examined_rows;

      table->sort.io_cache = (IO_CACHE *) my_malloc(sizeof(IO_CACHE),
						    MYF(MY_FAE | MY_ZEROFILL));
      if (!(sortorder=make_unireg_sortorder(order, &length, NULL)) ||
          (table->sort.found_records= filesort(thd, table, sortorder, length,
                                               select, limit, 1,
                                               &examined_rows))
          == HA_POS_ERROR)
 ......
......

This bug is a duplicate of the bug #36569.