Bug #18940 STRAIGHT_JOIN generates bad execution plan in 5.0
Submitted: 10 Apr 2006 12:55 Modified: 13 May 2006 23:01
Reporter: Oli Sennhauser Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.0.19 OS:Linux (linux)
Assigned to: Igor Babaev

[10 Apr 2006 12:55] Oli Sennhauser
Description:
Query with STRAIGHT_JOIN in 5.0.19 generates catastopical bad execution plan. Statement stagnate in state "preparing".
On 4.1.18 similar behaviour only happens under high load with concurrent inserts.
accoring sbester and domas this state is also wrong/a bug.

How to repeat:
setup can be delivered. is reproduced by sbester and domas.
[10 Apr 2006 14:49] Valerii Kravchuk
Thank you for a problem report. Can you provide a SHOW CREATE TABLE and SHOW TABLE STATUS results for all the tables involved?
[11 Apr 2006 6:54] Oli Sennhauser
I will attach it immediatly. Data follow soon too.
[11 Apr 2006 7:06] Oli Sennhauser
xac

Attachment: xac (application/octet-stream, text), 200.00 KiB.

[18 Apr 2006 4:30] Igor Babaev
I simulated the efect of equality propagation for 4.1 anf got the same problem:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.19-debug |
+--------------+
1 row in set (0.00 sec)

mysql> explain select straight_join * from main join main_groups on (main.id=main_groups.fk_mainid)
    ->           where main.id>0 and main_groups.fk_mainid>0;
+----+-------------+-------------+------+----------------------------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys                    | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+----------------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | main        | ALL  | PRIMARY,id_MT,ix3,id             | NULL |    NULL | NULL |   50 | Using where |
|  1 | SIMPLE      | main_groups | ALL  | fk_MainID,id_ispic,mainid_mt_grp | NULL |    NULL | NULL |   75 | Using where |
+----+-------------+-------------+------+----------------------------------+------+---------+------+------+-------------+

Thus the bug was present in 4.1 as well. Equality propagation introduced in 5.0 just
revealed it.
However I'm planning to fix the problem only in 5.0 in order not to affect applications in production
that use 4.1.
[28 Apr 2006 15:16] Sergey Petrunya
See also BUG#17379
[12 May 2006 1:25] Igor Babaev
I managed to build a simpler test case where this problem could be
demonstrated:

mysql> CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX(b));
Query OK, 0 rows affected (0.27 sec)

mysql> INSERT INTO t1 VALUES (1, 3), (9,4), (7,5), (4,5), (6,2),
    ->                       (3,1), (5,1), (8,9), (2,2), (0,9);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE t2 (c int, d int, f int, INDEX(c,f));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t2 VALUES
    ->  (1,0,0), (1,0,1), (2,0,0), (2,0,1), (3,0,0), (4,0,1),
    ->  (5,0,0), (5,0,1), (6,0,0), (0,0,1), (7,0,0), (7,0,1),
    ->  (0,0,0), (0,0,1), (8,0,0), (8,0,1), (9,0,0), (9,0,1);
Query OK, 18 rows affected (0.00 sec)
Records: 18  Duplicates: 0  Warnings: 0

mysql>
mysql> EXPLAIN
    -> SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;
+----+-------------+-------+-------+---------------+------+---------+-----------
+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref      
| rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+-----------
+------+-------------+
|  1 | SIMPLE      | t1    | range | PRIMARY,b     | b    | 5       | NULL     
|    3 | Using where |
|  1 | SIMPLE      | t2    | ref   | c             | c    | 5       | test.t1.a
|    2 | Using where |
+----+-------------+-------+-------+---------------+------+---------+-----------
+------+-------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN
    -> SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;
+----+-------------+-------+-------+---------------+------+---------+------+----
--+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  |
rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+-------------+
|  1 | SIMPLE      | t1    | range | PRIMARY,b     | b    | 5       | NULL |   
3 | Using where |
|  1 | SIMPLE      | t2    | ALL   | c             | NULL | NULL    | NULL |  
14 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+----
--+-------------+
2 rows in set (0.00 sec)
[12 May 2006 2:47] 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/6273
[13 May 2006 19:21] Igor Babaev
ChangeSet
  1.2126 06/05/11 19:47:00 igor@rurik.mysql.com +2 -0
  Added a test case for bug #18940:in 5.0 the optimizer chose
  a worse execution plan than in 4.1 for some queries.
  It happened due the fact that at some conditions the 
  optimizer always preferred range or full index scan access
  methods to lookup access methods even when the latter were much
  cheaper. 
  The problem was not observed in 4.1 for the reported query
  because the WHERE condition was not of a form that could
  cause the problem.
  Equality propagation introduced on 5.0 added an extra 
  predicate and changed the WHERE condition. The new condition
  provoked the optimizer to make a bad choice.

The problem was fixed by the patch for bug 17379.

The bug will be fixed in 5.0.22 and 5.1.10
[13 May 2006 23:01] Paul Dubois
Noted in 5.0.22, 5.1.10 changelogs.