| Bug #36569 | UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required | ||
|---|---|---|---|
| Submitted: | 7 May 2008 15:32 | Modified: | 29 Jul 2010 19:06 |
| Reporter: | Harrison Fisk | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 5.0.56sp1 | OS: | Any |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
[7 May 2008 15:32]
Harrison Fisk
[27 Jun 2008 10:06]
Sergey Petrunya
See also BUG#30584
[27 Oct 2008 16:59]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/57129 2704 Gleb Shchepa 2008-10-27 Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required The server uses filesort in single table UPDATE query even for already sorted data. The mysql_update function has been modified to eliminate double sorting of data that is already sorted in a proper order.
[28 Oct 2008 7:04]
Simon Mudd
In which version of MySQL is it expected that this fix will be released?
[19 Nov 2008 22:16]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/59308 2704 Gleb Shchepa 2008-11-20 Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required The server uses filesort in single table UPDATE query even for already sorted data. NOTE: this bugfix is disabled by default. To enable it the new server command line switch has been introduced: --with-bugfix=36569 (or with-bugfix=36569 in my.cnf) If this switch is used with unknown bug number, the server prints a warning to stderr: This version doesn't have ability to switch fix BUG#N, see http://bugs.mysql.com/bug.php?id=N The mysql_update function has been modified to eliminate double sorting of data that is already sorted in a proper order.
[20 Nov 2008 7:56]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/59316 2704 Gleb Shchepa 2008-11-20 Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required The server uses filesort in single table UPDATE query even for already sorted data. NOTE: this bugfix is disabled by default. To enable it the new server command line switch has been introduced: --with-bugfix=36569 (or with-bugfix=36569 in my.cnf) If this switch is used with unknown bug number, the server prints a warning to stderr: This version doesn't have ability to switch fix BUG#N, see http://bugs.mysql.com/bug.php?id=N The mysql_update function has been modified to eliminate double sorting of data that is already sorted in a proper order.
[11 Dec 2008 20:40]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/61409 2732 Gleb Shchepa 2008-12-12 Bug #30584: delete with order by and limit clauses does not use limit efficiently Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required During the Execution of single-table UPDATE and DELETE statements with ORDER BY clause an optimizer used the filesort algorithm even if not required. Extra optimization has been added.
[22 Jan 2009 15:01]
Georgi Kodinov
Bug #30584 will be fixed by the fix for this bug
[4 Feb 2009 14:12]
Sergey Petrunya
If we take with-bugfix approach as a non-possibility (which is a reasonable thing to do as it does not exist yet), and consider only pushing the fix unconditionally, then we'll have to conclude that it's a high-risk fix. Updated the risk parameter, please re-triage.
[9 Feb 2009 15:37]
Georgi Kodinov
Let me try to clarify Sergey's words: To fix this we need to add a non-cost based rule in the MySQL optimizer. If this is done unconditionally it may affect other uses of the UPDATE command. Since currently MySQL lacks a proper set of optimization hints fixing this is too risky in the current 5.x released binaries (as it may result in other behavior changes of the optimizer).
[7 Mar 2010 14:13]
Domas Mituzas
'ping'. this is quite critical flaw in MySQL, how can it be 'patch pending' for more than a year?
[7 Mar 2010 15:13]
Mark Callaghan
I have an idea for an easy short-term fix. Make the UPDATE and DELETE statements fail when they will do a full table scan despite the LIMIT clause -- see http://bugs.mysql.com/bug.php?id=30584 The lack of EXPLAIN for insert, update, delete makes problems like this much more likely to reach production.
[11 Mar 2010 6:36]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/102955 3119 Gleb Shchepa 2010-03-11 Bug #30584: delete with order by and limit clauses does not use limit efficiently Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required During the Execution of single-table UPDATE and DELETE statements with ORDER BY clause an optimizer used the filesort algorithm even if not required. Extra optimization has been added. @ mysql-test/r/single_delete_update.result Test case for bug #30584 and bug #36569. @ mysql-test/t/single_delete_update.test Test case for bug #30584 and bug #36569. @ sql/opt_range.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required The QUICK_SELECT_I::is_ordered_by function has been added. @ sql/opt_range.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required The QUICK_SELECT_I::is_ordered_by function has been added. @ sql/sql_delete.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_delete: an optimization has been added to skip double-sorting with ORDER BY clause where a select result ordering is acceptable. @ sql/sql_select.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required 1. The const_expression_in_where function has been modified to accept both Item and Field pointers via intermediate wrap_field_or_item class object. 2. Public const_in_where functions have been added. 3. Static test_if_order_by_key function has been moved to public scope. Also a part of this function has been extracted as a new st_table::get_order_by_usable_keys method. @ sql/sql_select.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required 1. test_if_order_by_key() has been moved to public scope. 2. New const_in_where functions have been added. @ sql/sql_update.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_update: an optimization has been added to skip double-sorting with ORDER BY clause where a select result ordering is acceptable. @ sql/table.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required st_table::const_key_parts, st_table::update_const_key_parts, st_table::get_order_by_usable_keys, st_table::simple_test_if_skip_sort_order and st_order::simple_remove_const methods have been added. @ sql/table.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required st_table::const_key_parts, st_table::update_const_key_parts, st_table::get_order_by_usable_keys, st_table::simple_test_if_skip_sort_order and st_order::simple_remove_const methods have been added.
[11 Mar 2010 15:56]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/103029 3119 Gleb Shchepa 2010-03-11 Bug #30584: delete with order by and limit clauses does not use limit efficiently Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required During the Execution of single-table UPDATE and DELETE statements with ORDER BY clause an optimizer used the filesort algorithm even if not required. Extra optimization has been added: when applicable, single-table UPDATE/DELETE statements use existing index instead of filesort like similar SELECT queries do (excluding cases when UPDATE is trying to update its sorting key). I.e. from now most of single-table UPDATE & DELETE statements should show same SHOW STATUS LIKE 'Sort%' output as similar SELECT queries. @ mysql-test/r/single_delete_update.result Test case for bug #30584 and bug #36569. @ mysql-test/t/single_delete_update.test Test case for bug #30584 and bug #36569. @ sql/opt_range.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required The QUICK_SELECT_I::is_ordered_by function has been added. @ sql/opt_range.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required The QUICK_SELECT_I::is_ordered_by function has been added. @ sql/sql_delete.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_delete: an optimization has been added to skip double-sorting with ORDER BY clause where a select result ordering is acceptable. @ sql/sql_select.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required 1. The const_expression_in_where function has been modified to accept both Item and Field pointers via intermediate wrap_field_or_item class object. 2. Public const_item_in_where and const_field_in_where functions have been added. 3. Static test_if_order_by_key function has been moved to public scope. Also a part of this function has been extracted as a new st_table::get_order_by_usable_keys method. @ sql/sql_select.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required 1. test_if_order_by_key() has been moved to public scope. 2. Public const_item_in_where and const_field_in_where functions have been added. @ sql/sql_update.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_update: an optimization has been added to skip double-sorting with ORDER BY clause where a select result ordering is acceptable. @ sql/table.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required st_table::const_key_parts, st_table::update_const_key_parts, st_table::get_order_by_usable_keys, st_table::simple_test_if_skip_sort_order and st_order::simple_remove_const methods have been added. @ sql/table.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required st_table::const_key_parts, st_table::update_const_key_parts, st_table::get_order_by_usable_keys, st_table::simple_test_if_skip_sort_order and st_order::simple_remove_const methods have been added.
[16 Mar 2010 8:59]
Manyi Lu
Risk and effort assessment for fixing it in 5.5.
[17 Mar 2010 10:48]
Gleb Shchepa
Bug #51808 has been marked as a duplicate of this bug.
[31 Mar 2010 12:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/104705 3128 Gleb Shchepa 2010-03-31 Bug #30584: delete with order by and limit clauses does not use limit efficiently Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required During the execution of single-table UPDATE and DELETE statements with ORDER BY clause an optimizer used the filesort algorithm even if not required. Extra optimization has been added: when applicable, single-table UPDATE/DELETE statements use existing index instead of filesort like similar SELECT queries do. I.e. from now most of single-table UPDATE & DELETE statements should show same SHOW STATUS LIKE 'Sort%' output as similar SELECT queries. @ mysql-test/r/single_delete_update.result Test case for bug #30584 and bug #36569. @ mysql-test/r/update.result Updated result for optimized query, bug #30584. Note: "Handler_read_last 1" omitted, see bug 52312: lost Handler_read_last status variable. @ mysql-test/t/single_delete_update.test Test case for bug #30584 and bug #36569. @ sql/opt_range.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_RANGE_SELECT::make_reverse() * QUICK_SELECT_I::is_ordered_by() @ sql/opt_range.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_SELECT_I::make_reverse() * QUICK_SELECT_I::is_ordered_by() * SQL_SELECT::set_quick() @ sql/records.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * init_read_record_idx() has been modified to allow reverse index scan New functions: * rr_index_last() * rr_index_desc() @ sql/records.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required init_read_record_idx() has been modified to allow reverse index scan @ sql/sql_delete.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_delete: an optimization has been added to skip double-sorting with ORDER BY clause where select result ordering is acceptable. @ sql/sql_select.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * The const_expression_in_where function has been modified to accept both Item and Field pointers via intermediate wrap_field_or_item class object. * Static test_if_order_by_key function has been moved to public scope to share with QUICK_SELECT_I::is_ordered_by(). New functions: * get_index_for_order() * test_if_cheaper_ordering() has been extracted from test_if_skip_sort_order() to share with get_index_for_order() * simple_remove_const() * const_item_in_where() and const_field_in_where() * new internal wrapper class: wrap_field_or_item. @ sql/sql_select.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * test_if_order_by_key() has been moved to public scope. New functions: * test_if_cheaper_ordering() * const_field_in_where() and const_item_in_where() * simple_remove_const() * get_index_for_order() @ sql/sql_update.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_update: an optimization has been added to skip double-sorting with ORDER BY clause where a select result ordering is acceptable. @ sql/table.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order() @ sql/table.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order()
[9 Apr 2010 14:05]
Martin Hansson
Review sent by email.
[16 Apr 2010 9:37]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/105837 3140 Gleb Shchepa 2010-04-16 Bug #30584: delete with order by and limit clauses does not use limit efficiently Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required Execution of single-table UPDATE and DELETE statements did not use the same optimizer as was used in the compilation of SELECT statements. Instead, it had an optimizer of its own that did not take into account that you can omit sorting by retrieving rows using an index. Extra optimization has been added: when applicable, single-table UPDATE/DELETE statements use an existing index instead of filesort. A corresponding SELECT query would do the former. Also handling of the DESC ordering expression has been added when reverse index scan is applicable. From now on most single table UPDATE and DELETE statements show the same disk access patterns as the corresponding SELECT query. We verify this by comparing the result of SHOW STATUS LIKE 'Sort% Currently the get_index_for_order function a) checks quick select index (if any) for compatibility with the ORDER expression list or b) chooses the cheapest available compatible index, but only if the index scan is cheaper than filesort. Second way is implemented by the new test_if_cheaper_ordering function (extracted part the test_if_skip_sort_order()). @ mysql-test/r/single_delete_update.result Test case for bug #30584 and bug #36569. @ mysql-test/r/update.result Updated result for optimized query, bug #30584. Note: "Handler_read_last 1" omitted, see bug 52312: lost Handler_read_last status variable. @ mysql-test/t/single_delete_update.test Test case for bug #30584 and bug #36569. @ sql/opt_range.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_RANGE_SELECT::make_reverse() * QUICK_SELECT_I::is_ordered_by() @ sql/opt_range.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_SELECT_I::make_reverse() * QUICK_SELECT_I::is_ordered_by() * SQL_SELECT::set_quick() @ sql/records.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * init_read_record_idx() has been modified to allow reverse index scan New functions: * rr_index_last() * rr_index_desc() @ sql/records.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required init_read_record_idx() has been modified to allow reverse index scan @ sql/sql_delete.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_delete: an optimization has been added to skip unnecessary sorting with ORDER BY clause where select result ordering is acceptable. @ sql/sql_select.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * The const_expression_in_where function has been modified to accept both Item and Field pointers via intermediate wrap_field_or_item class object. * Static test_if_order_by_key function has been moved to public scope to share with QUICK_SELECT_I::is_ordered_by(). New functions: * get_index_for_order() * test_if_cheaper_ordering() has been extracted from test_if_skip_sort_order() to share with get_index_for_order() * simple_remove_const() * const_item_in_where() and const_field_in_where() * new internal wrapper class: wrap_field_or_item. @ sql/sql_select.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * test_if_order_by_key() has been moved to public scope. New functions: * test_if_cheaper_ordering() * const_field_in_where() and const_item_in_where() * simple_remove_const() * get_index_for_order() @ sql/sql_update.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_update: an optimization has been added to skip unnecessary sorting with ORDER BY clause where a select result ordering is acceptable. @ sql/table.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order() @ sql/table.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order()
[20 Apr 2010 17:12]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/106143 3140 Gleb Shchepa 2010-04-20 Bug #30584: delete with order by and limit clauses does not use limit efficiently Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required Execution of single-table UPDATE and DELETE statements did not use the same optimizer as was used in the compilation of SELECT statements. Instead, it had an optimizer of its own that did not take into account that you can omit sorting by retrieving rows using an index. Extra optimization has been added: when applicable, single-table UPDATE/DELETE statements use an existing index instead of filesort. A corresponding SELECT query would do the former. Also handling of the DESC ordering expression has been added when reverse index scan is applicable. From now on most single table UPDATE and DELETE statements show the same disk access patterns as the corresponding SELECT query. We verify this by comparing the result of SHOW STATUS LIKE 'Sort% Currently the get_index_for_order function a) checks quick select index (if any) for compatibility with the ORDER expression list or b) chooses the cheapest available compatible index, but only if the index scan is cheaper than filesort. Second way is implemented by the new test_if_cheaper_ordering function (extracted part the test_if_skip_sort_order()). @ mysql-test/r/single_delete_update.result Test case for bug #30584 and bug #36569. @ mysql-test/r/update.result Updated result for optimized query, bug #30584. Note: "Handler_read_last 1" omitted, see bug 52312: lost Handler_read_last status variable. @ mysql-test/t/single_delete_update.test Test case for bug #30584 and bug #36569. @ sql/opt_range.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_RANGE_SELECT::make_reverse() * QUICK_SELECT_I::is_ordered_by() @ sql/opt_range.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_SELECT_I::make_reverse() * QUICK_SELECT_I::is_ordered_by() * SQL_SELECT::set_quick() @ sql/records.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * init_read_record_idx() has been modified to allow reverse index scan New functions: * rr_index_last() * rr_index_desc() @ sql/records.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required init_read_record_idx() has been modified to allow reverse index scan @ sql/sql_delete.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_delete: an optimization has been added to skip unnecessary sorting with ORDER BY clause where select result ordering is acceptable. @ sql/sql_select.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * The const_expression_in_where function has been modified to accept both Item and Field pointers. * Static test_if_order_by_key function has been moved to public scope to share with QUICK_SELECT_I::is_ordered_by(). New functions: * get_index_for_order() * test_if_cheaper_ordering() has been extracted from test_if_skip_sort_order() to share with get_index_for_order() * simple_remove_const() @ sql/sql_select.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * test_if_order_by_key() and const_expression_in_where() have been moved to public scope. New functions: * test_if_cheaper_ordering() * simple_remove_const() * get_index_for_order() @ sql/sql_update.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_update: an optimization has been added to skip unnecessary sorting with ORDER BY clause where a select result ordering is acceptable. @ sql/table.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order() @ sql/table.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order()
[21 Apr 2010 11:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/106247 3140 Gleb Shchepa 2010-04-21 Bug #30584: delete with order by and limit clauses does not use limit efficiently Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required Execution of single-table UPDATE and DELETE statements did not use the same optimizer as was used in the compilation of SELECT statements. Instead, it had an optimizer of its own that did not take into account that you can omit sorting by retrieving rows using an index. Extra optimization has been added: when applicable, single-table UPDATE/DELETE statements use an existing index instead of filesort. A corresponding SELECT query would do the former. Also handling of the DESC ordering expression has been added when reverse index scan is applicable. From now on most single table UPDATE and DELETE statements show the same disk access patterns as the corresponding SELECT query. We verify this by comparing the result of SHOW STATUS LIKE 'Sort% Currently the get_index_for_order function a) checks quick select index (if any) for compatibility with the ORDER expression list or b) chooses the cheapest available compatible index, but only if the index scan is cheaper than filesort. Second way is implemented by the new test_if_cheaper_ordering function (extracted part the test_if_skip_sort_order()). @ mysql-test/r/single_delete_update.result Test case for bug #30584 and bug #36569. @ mysql-test/r/update.result Updated result for optimized query, bug #30584. Note: "Handler_read_last 1" omitted, see bug 52312: lost Handler_read_last status variable. @ mysql-test/t/single_delete_update.test Test case for bug #30584 and bug #36569. @ sql/opt_range.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_RANGE_SELECT::make_reverse() * QUICK_SELECT_I::is_ordered_by() @ sql/opt_range.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_SELECT_I::make_reverse() * QUICK_SELECT_I::is_ordered_by() * SQL_SELECT::set_quick() @ sql/records.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * init_read_record_idx() has been modified to allow reverse index scan New functions: * rr_index_last() * rr_index_desc() @ sql/records.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required init_read_record_idx() has been modified to allow reverse index scan @ sql/sql_delete.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_delete: an optimization has been added to skip unnecessary sorting with ORDER BY clause where select result ordering is acceptable. @ sql/sql_select.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * The const_expression_in_where function has been modified to accept both Item and Field pointers. * Static test_if_order_by_key function has been moved to public scope to share with QUICK_SELECT_I::is_ordered_by(). New functions: * get_index_for_order() * test_if_cheaper_ordering() has been extracted from test_if_skip_sort_order() to share with get_index_for_order() * simple_remove_const() @ sql/sql_select.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * test_if_order_by_key() and const_expression_in_where() have been moved to public scope. New functions: * test_if_cheaper_ordering() * simple_remove_const() * get_index_for_order() @ sql/sql_update.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_update: an optimization has been added to skip unnecessary sorting with ORDER BY clause where a select result ordering is acceptable. @ sql/table.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order() @ sql/table.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order()
[6 May 2010 14:16]
Philip Stoev
see bug #53450
[17 May 2010 16:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/108458 3140 Gleb Shchepa 2010-05-17 Bug #30584: delete with order by and limit clauses does not use limit efficiently Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required Execution of single-table UPDATE and DELETE statements did not use the same optimizer as was used in the compilation of SELECT statements. Instead, it had an optimizer of its own that did not take into account that you can omit sorting by retrieving rows using an index. Extra optimization has been added: when applicable, single-table UPDATE/DELETE statements use an existing index instead of filesort. A corresponding SELECT query would do the former. Also handling of the DESC ordering expression has been added when reverse index scan is applicable. From now on most single table UPDATE and DELETE statements show the same disk access patterns as the corresponding SELECT query. We verify this by comparing the result of SHOW STATUS LIKE 'Sort% Currently the get_index_for_order function a) checks quick select index (if any) for compatibility with the ORDER expression list or b) chooses the cheapest available compatible index, but only if the index scan is cheaper than filesort. Second way is implemented by the new test_if_cheaper_ordering function (extracted part the test_if_skip_sort_order()). @ mysql-test/r/single_delete_update.result Test case for bug #30584 and bug #36569. @ mysql-test/r/update.result Updated result for optimized query, bug #30584. Note: "Handler_read_last 1" omitted, see bug 52312: lost Handler_read_last status variable. @ mysql-test/t/single_delete_update.test Test case for bug #30584 and bug #36569. @ sql/opt_range.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_RANGE_SELECT::make_reverse() * QUICK_SELECT_I::is_ordered_by() @ sql/opt_range.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_SELECT_I::make_reverse() * QUICK_SELECT_I::is_ordered_by() * SQL_SELECT::set_quick() @ sql/records.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * init_read_record_idx() has been modified to allow reverse index scan New functions: * rr_index_last() * rr_index_desc() @ sql/records.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required init_read_record_idx() has been modified to allow reverse index scan @ sql/sql_delete.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_delete: an optimization has been added to skip unnecessary sorting with ORDER BY clause where select result ordering is acceptable. @ sql/sql_select.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * The const_expression_in_where function has been modified to accept both Item and Field pointers. * Static test_if_order_by_key function has been moved to public scope to share with QUICK_SELECT_I::is_ordered_by(). New functions: * get_index_for_order() * test_if_cheaper_ordering() has been extracted from test_if_skip_sort_order() to share with get_index_for_order() * simple_remove_const() @ sql/sql_select.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * test_if_order_by_key() and const_expression_in_where() have been moved to public scope. New functions: * test_if_cheaper_ordering() * simple_remove_const() * get_index_for_order() @ sql/sql_update.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_update: an optimization has been added to skip unnecessary sorting with ORDER BY clause where a select result ordering is acceptable. @ sql/table.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order() @ sql/table.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order()
[18 May 2010 9:36]
Philip Stoev
When applying the patch for bug 36569 from http://lists.mysql.com/commits/108458 to revision-id: alik@sun.com-20100507091823-nzao4h3qosau4tin date: 2010-05-07 13:18:23 +0400 build-date: 2010-05-18 12:20:11 +0300 revno: 3143 branch-nick: mysql-next-mr-bug36569 bzr: warning: some compiled extensions could not be loaded; see <https://answers.launchpad.net/bzr/+faq/703> a performance regression was found, filed as new bug #53737
[18 May 2010 10:13]
Philip Stoev
The regression mentioned previously by Patrick , where a different number of affected_rows is reported has now been isolated and filed as a new bug #53742
[21 May 2010 10:25]
Philip Stoev
YY grammar for 36569
Attachment: bug36569-2.yy (application/octet-stream, text), 3.31 KiB.
[21 May 2010 10:25]
Philip Stoev
ZZ file for 36569
Attachment: bug36569-2.zz (application/octet-stream, text), 1.07 KiB.
[21 May 2010 10:28]
Philip Stoev
To run a combined result set and performance comparison test , please pull fresh from the RQG repository and use the following command line: $ perl ./runall.pl \ --threads=1 \ --basedir1=/build/bzr/mysql-next-mr-bug36569 \ --basedir2=/build/bzr/mysql-next-mr \ --Validator=ResultsetComparator,ExecutionTimeComparator \ --grammar=conf/optimizer/bug36569-2.yy \ --gendata=conf/optimizer/bug36569-2.zz \ --queries=10K \ --mysqld1=--innodb-flush-log-at-trx-commit=0 \ --mysqld2=--innodb-flush-log-at-trx-commit=0 \ --queries=1M If the table size is set to 1000 in the .ZZ file, more queries will be run per hour and functional bugs will be more easily exposed. If it is set to 1000000 , performance improvements and regressions will be more apparent.
[13 Jun 2010 15:58]
James Day
See bug #53737 and bug #53742 for some negative effects of the current version of the patch. Work continues.
[17 Jun 2010 12:37]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/111436 3245 Gleb Shchepa 2010-06-16 Bug #30584: delete with order by and limit clauses does not use limit efficiently Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required Execution of single-table UPDATE and DELETE statements did not use the same optimizer as was used in the compilation of SELECT statements. Instead, it had an optimizer of its own that did not take into account that you can omit sorting by retrieving rows using an index. Extra optimization has been added: when applicable, single-table UPDATE/DELETE statements use an existing index instead of filesort. A corresponding SELECT query would do the former. Also handling of the DESC ordering expression has been added when reverse index scan is applicable. From now on most single table UPDATE and DELETE statements show the same disk access patterns as the corresponding SELECT query. We verify this by comparing the result of SHOW STATUS LIKE 'Sort% Currently the get_index_for_order function a) checks quick select index (if any) for compatibility with the ORDER expression list or b) chooses the cheapest available compatible index, but only if the index scan is cheaper than filesort. Second way is implemented by the new test_if_cheaper_ordering function (extracted part the test_if_skip_sort_order()). @ mysql-test/r/single_delete_update.result Test case for bug #30584 and bug #36569. @ mysql-test/r/update.result Updated result for optimized query, bug #30584. Note: "Handler_read_last 1" omitted, see bug 52312: lost Handler_read_last status variable. @ mysql-test/t/single_delete_update.test Test case for bug #30584 and bug #36569. @ sql/opt_range.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_RANGE_SELECT::make_reverse() * QUICK_SELECT_I::is_ordered_by() @ sql/opt_range.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_SELECT_I::make_reverse() * QUICK_SELECT_I::is_ordered_by() * SQL_SELECT::set_quick() @ sql/records.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * init_read_record_idx() has been modified to allow reverse index scan New functions: * rr_index_last() * rr_index_desc() @ sql/records.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required init_read_record_idx() has been modified to allow reverse index scan @ sql/sql_delete.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_delete: an optimization has been added to skip unnecessary sorting with ORDER BY clause where select result ordering is acceptable. @ sql/sql_select.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * The const_expression_in_where function has been modified to accept both Item and Field pointers. * Static test_if_order_by_key function has been moved to public scope to share with QUICK_SELECT_I::is_ordered_by(). New functions: * get_index_for_order() * test_if_cheaper_ordering() has been extracted from test_if_skip_sort_order() to share with get_index_for_order() * simple_remove_const() @ sql/sql_select.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * test_if_order_by_key() and const_expression_in_where() have been moved to public scope. New functions: * test_if_cheaper_ordering() * simple_remove_const() * get_index_for_order() @ sql/sql_update.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_update: an optimization has been added to skip unnecessary sorting with ORDER BY clause where a select result ordering is acceptable. @ sql/table.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order() @ sql/table.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order()
[22 Jun 2010 21:06]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/111832 3069 Gleb Shchepa 2010-06-23 Bug #30584: delete with order by and limit clauses does not use limit efficiently Bug #36569: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required Also two bugs reported after QA review (before the commit of bugs above to public trees, no documentation needed): Bug #53737: Performance regressions after applying patch for bug 36569 Bug #53742: UPDATEs have no effect after applying patch for bug 36569 Execution of single-table UPDATE and DELETE statements did not use the same optimizer as was used in the compilation of SELECT statements. Instead, it had an optimizer of its own that did not take into account that you can omit sorting by retrieving rows using an index. Extra optimization has been added: when applicable, single-table UPDATE/DELETE statements use an existing index instead of filesort. A corresponding SELECT query would do the former. Also handling of the DESC ordering expression has been added when reverse index scan is applicable. From now on most single table UPDATE and DELETE statements show the same disk access patterns as the corresponding SELECT query. We verify this by comparing the result of SHOW STATUS LIKE 'Sort% Currently the get_index_for_order function a) checks quick select index (if any) for compatibility with the ORDER expression list or b) chooses the cheapest available compatible index, but only if the index scan is cheaper than filesort. Second way is implemented by the new test_if_cheaper_ordering function (extracted part the test_if_skip_sort_order()). @ mysql-test/r/log_state.result Updated result for optimized query, bug #36569. @ mysql-test/r/single_delete_update.result Test case for bug #30584, bug #36569 and bug #53742. @ mysql-test/r/update.result Updated result for optimized query, bug #30584. Note: "Handler_read_last 1" omitted, see bug 52312: lost Handler_read_last status variable. @ mysql-test/t/single_delete_update.test Test case for bug #30584, bug #36569 and bug #53742. @ sql/opt_range.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New QUICK_RANGE_SELECT::make_reverse method has been added. @ sql/opt_range.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * get_index_for_order() has been rewritten entirely and moved to sql_select.cc New functions: * QUICK_SELECT_I::make_reverse() * SQL_SELECT::set_quick() @ sql/records.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required * init_read_record_idx() has been modified to allow reverse index scan New functions: * rr_index_last() * rr_index_desc() @ sql/records.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required init_read_record_idx() has been modified to allow reverse index scan @ sql/sql_delete.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_delete: an optimization has been added to skip unnecessary sorting with ORDER BY clause where select result ordering is acceptable. @ sql/sql_select.cc Bug #30584, bug #36569, bug #53737, bug #53742: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required The const_expression_in_where function has been modified to accept both Item and Field pointers. New functions: * get_index_for_order() * test_if_cheaper_ordering() has been extracted from test_if_skip_sort_order() to share with get_index_for_order() * simple_remove_const() @ sql/sql_select.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * test_if_cheaper_ordering() * simple_remove_const() * get_index_for_order() @ sql/sql_update.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required mysql_update: an optimization has been added to skip unnecessary sorting with ORDER BY clause where a select result ordering is acceptable. @ sql/table.cc Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order() @ sql/table.h Bug #30584, bug #36569: UPDATE/DELETE ... WHERE ... ORDER BY... always does a filesort even if not required New functions: * TABLE::update_const_key_parts() * is_simple_order()
[23 Jul 2010 12:28]
Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:35]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[29 Jul 2010 19:06]
Paul DuBois
Noted in 5.5.6 changelog. Statements of the form UPDATE ... WHERE ... ORDER BY used a filesort even when not required.
[28 May 2014 7:58]
Valeriy Kravchuk
Why is this closed if it is still repeatable with 5.5.36 (ad, as some users report, recent 5.6.x as well): C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.5.36 MySQL Community Server (GPL) 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 (1.20 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.19 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> mysql> -- no filesort 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.13 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.08 sec) mysql> mysql> -- does an unneeded filesort mysql> UPDATE update_test SET a = a + 10 WHERE b = 1 ORDER BY c LIMIT 2; Query OK, 2 rows affected (0.11 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)
[30 May 2014 11:12]
MySQL Verification Team
Opened new Bug #72815 after the last note
[19 Jun 2014 23:20]
Paul DuBois
Addition to changelog entry: Prior to this fix, index hints were accepted for UPDATE statements but were ignored. Now they are used.
