MySQL Bugs: #18940: STRAIGHT_JOIN generates bad execution plan in 5.0

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	CPU Architecture:	Any

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.

Thank you for a problem report. Can you provide a SHOW CREATE TABLE and SHOW TABLE STATUS results for all the tables involved?

I will attach it immediatly. Data follow soon too.

xac

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

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.

See also BUG#17379

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)

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

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

Noted in 5.0.22, 5.1.10 changelogs.