MySQL Bugs: #53737: Performance regressions after applying patch for bug 36569

Bug #53737	Performance regressions after applying patch for bug 36569
Submitted:	18 May 2010 9:30	Modified:	29 Jul 2010 19:12
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	mysql-next-mr	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
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>

the list of queries below exhibited a massive performance degradation, whereby the unpatched version is able to execute them immediately, whereas the patched version takes several seconds per query.

How to repeat:
The dataset and the queries will be uploaded shortly. The format of the query list file is as follows:

time0 = time required to run query with patch
time1 = time required to run query without patch
ratio = time0/time1
query = the test of the query itself

Query list for bug 53737

Attachment: bug53737-queries.txt (text/plain), 3.10 KiB.

Data dump:

http://mysql-systemqa.s3.amazonaws.com/bug53737.zip

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/111432

3246 Gleb Shchepa	2010-06-17
      Bug #53737: Performance regressions after applying patch for bug 36569    
                                                                                                           
      Some single table UPDATE/SELECT queries those use quick select method                                
      that sends result data in the order incompatible with ORDER BY clause                                
      were affected by the performance degradation after applying the patch                                
      for the bug 36569.                                                                                   
                                                                                                           
      By the mistake the quick select object for such queries was freed                                    
      and cleaned up ignoring the fact that filesort uses quick select                                     
      data intensively for speed.                                                                          
                                                                                                           
      The get_index_for_order function has been modified to not to                                         
      remove quick select objects even in the case where quick select                                      
      ordering is incompatible with a desired ORDER BY ordering.         
     @ sql/sql_select.cc
        Bug #53737: Performance regressions after applying patch for bug 36569                               
                                                                                                             
        The get_index_for_order function has been modified to not to                                         
        remove quick select objects even in the case where quick select                                      
        ordering is incompatible with a desired ORDER BY ordering.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)

Noted in 5.5.6 changelog.

The patch for Bug#36569 caused performance regressions and incorrect
execution of some UPDATE statments.