Bug #27531 Query performance degredation in 4.1.22 and greater
Submitted: 29 Mar 2007 18:00 Modified: 11 May 2007 18:54
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.22 5.0 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: bfsm_2007_04_05, bfsm_2007_04_19, DoS

[29 Mar 2007 18:00] Todd Farmer
Description:
In 4.1.22, you have fixed the following bug:

     * Use of the join cache in favor of an index for ORDER BY
       operations could cause incorrect result sorting.
       (Bug#17212: http://bugs.mysql.com/17212)

A commit is the following one:

http://lists.mysql.com/commits/8916

This truly made sorting correct again , but it had DEVASTATING effects
on the performance. See the example in "how to repeat".

This is observed on both Windows and Linux platforms.

How to repeat:

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 4.1.21-community-nt |
+---------------------+
1 row in set (0.00 sec)

mysql> select  SQL_NO_CACHE
    ->         oid_id,
    ->         enum_name,
    ->         enum_value,
    ->         enum_syntax
    -> from
    ->         type_enums,
    ->         mib_oids
    -> where
    ->         type_enums.module_id=1 and
    ->         type_enums.type_id=mib_oids.oid_type
    -> order by
    ->         type_id,
    ->         enum_order;
Empty set (0.02 sec)

mysql> select version();
+---------------+
| version()     |
+---------------+
| 4.1.22-pro-nt |
+---------------+
1 row in set (0.00 sec)

mysql> select  SQL_NO_CACHE
    ->         oid_id,
    ->         enum_name,
    ->         enum_value,
    ->         enum_syntax
    -> from
    ->         type_enums,
    ->         mib_oids
    -> where
    ->         type_enums.module_id=1 and
    ->         type_enums.type_id=mib_oids.oid_type
    -> order by
    ->         type_id,
    ->         enum_order;
Empty set (3 min 40.98 sec)

-----------------------------------------------------------------------------

So, from 0.02 seconds to almost 4 minutes. Nice. All versions since
4.1.22, including latest 5.0 are in the range of more then 3 minutes.
[10 Apr 2007 0:05] Trudy Pelzer
I'm removing the "regression" tag; it was added under
a misapprehension. Note that, without Joro's fix for
bug#17212, the relevant query returned the *wrong* result
in 0.02 seconds. With the fix, the query now returns 
the *correct* result, albeit slowly. We cannot consider it 
a regression if an incorrect result is returned much sooner 
than the correct result (no fair comparing the speed unless 
the same result is also returned).

Updating the optimizer so that this query will run much
faster *and* return the correct result is a larger task;
when this will be done still needs to be determined.
[30 Apr 2007 12:23] 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/25759

ChangeSet@1.2442, 2007-04-30 15:22:58+03:00, gkodinov@magare.gmz +3 -0
  Bug #27531:
  When checking for applicability of join cache
  we must disable its usage only if there is no
  temp table in use.
  When a temp table is used we can use join
  cache (and it will not make the result-set 
  unordered) to fill the temp table. The filesort() 
  operation is then applied to the data in the temp 
  table and hence is not affected by join cache
  usage.
  Fixed by narrowing the condition for disabling 
  join cache to exclude the case where temp table
  is used.
[30 Apr 2007 14:53] 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/25774

ChangeSet@1.2508, 2007-04-30 17:53:01+03:00, gkodinov@magare.gmz +31 -0
  bug #27531: 5.1 part of the fix:
   - added join cache indication in EXPLAIN (Extra column).
   - prefer filesort over full scan over 
     index for ORDER BY (because it's faster).
   - when switching from REF to RANGE because 
     RANGE uses longer key turn off sort on
     the head table only as the resulting 
     RANGE access is a candidate for join cache
     and we don't want to disable it by sorting
     on the first table only.
[4 May 2007 13:43] 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/26097

ChangeSet@1.2653, 2007-05-04 16:43:29+03:00, gkodinov@magare.gmz +3 -0
  Bug #27531: the 4.1 fix.
  When checking for applicability of join cache
  we must disable its usage only if there is no
  temp table in use.
  When a temp table is used we can use join
  cache (and it will not make the result-set 
  unordered) to fill the temp table. The filesort() 
  operation is then applied to the data in the temp 
  table and hence is not affected by join cache
  usage.
  Fixed by narrowing the condition for disabling 
  join cache to exclude the case where temp table
  is used.
[7 May 2007 18:15] Bugs System
Pushed into 5.1.18-beta
[10 May 2007 6:44] Bugs System
Pushed into 4.1.23
[10 May 2007 6:52] Bugs System
Pushed into 5.0.42
[11 May 2007 18:54] Paul DuBois
Noted in 4.1.23, 5.0.42, 5.1.18 changelogs.

The fix for Bug #17212 provided correct sort order for misordered
output of certain queries, but caused significant overall query
performance degradation. (Results were correct (good), but returned
much more slowly (bad).) The fix also affected performance of queries
for which results were correct. The performance degradation has been
addressed.

Also updated EXPLAIN section in 5.1 manual.
[29 May 2007 14:08] 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/27583

ChangeSet@1.2515, 2007-05-29 15:58:18+03:00, gkodinov@macbook.gmz +31 -0
  Bug #27531: 5.1 part of the fix
  - Renamed "Using join cache" to "Using join buffer".
  - "Using join buffer" is now printed on the last
    table that "reads" from the join buffer cache.
[4 Jun 2007 21:21] Bugs System
Pushed into 5.1.20-beta