enterprise-support team mailing list archive

[Valerii Kravchuk <valerii.kravchuk@xxxxxxxxxxx>]

This is easy to confirm with PS 5.6.17:

openxs@ao756:~/dbs/p5.6$ mysql -uroot -proot test
Warning: Using a password on the command line interface can be insecure.
Reading 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 1
Server version: 5.6.17-65.0-587.saucy (Ubuntu)

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE TABLE update_test (a INT AUTO_INCREMENT PRIMARY KEY, b INT, c INT,
    ->  INDEX (b,c));
Query OK, 0 rows affected (0,93 sec)

mysql> INSERT INTO update_test (b, c) VALUES (1, 1), (1, 2), (1,3), (2, 2), (2,
    -> 3), (2,4), (2, 5), (5, 5), (6,6), (7,7);
Query OK, 10 rows affected (0,08 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM update_test WHERE b = 1 ORDER BY c LIMIT 2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 |
| 2 |    1 |    2 |
+---+------+------+
2 rows in set (0,06 sec)

mysql> SHOW SESSION STATUS LIKE 'Sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 0     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0,00 sec)

mysql> explain SELECT * FROM update_test WHERE b = 1 ORDER BY c LIMIT 2;
+----+-------------+-------------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | update_test | ref  | b             | b    | 5       | const |    3 | Using where; Using index |
+----+-------------+-------------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0,02 sec)

mysql> UPDATE update_test SET a = a + 10 WHERE b = 1 ORDER BY c LIMIT 2;
Query OK, 2 rows affected (0,07 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SHOW SESSION STATUS LIKE 'Sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 1     |
| Sort_rows         | 2     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0,00 sec)

mysql> explain UPDATE update_test SET a = a + 10 WHERE b = 1 ORDER BY c LIMIT 2;
+----+-------------+-------------+-------+---------------+------+---------+-------+------+-----------------------------+
| id | select_type | table       | type  | possible_keys | key  | key_len | ref   | rows | Extra                       |
+----+-------------+-------------+-------+---------------+------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | update_test | range | b             | b    | 5       | const |    3 | Using where; Using filesort |
+----+-------------+-------------+-------+---------------+------+---------+-------+------+-----------------------------+
1 row in set (0,00 sec)


** Tags added: upstream

** Changed in: percona-server/5.6
       Status: New => Confirmed

** Bug watch added: MySQL Bug System #36569
   http://bugs.mysql.com/bug.php?id=36569

** Also affects: mysql-server via
   http://bugs.mysql.com/bug.php?id=36569
   Importance: Unknown
       Status: Unknown

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1324405

Title:
  UPDATE ... WHERE ... ORDER BY... always does a filesort when not
  required

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1324405/+subscriptions