MySQL Bugs: #43029: FORCE INDEX FOR ORDER BY is ignored when join buffering is used

Bug #43029	FORCE INDEX FOR ORDER BY is ignored when join buffering is used
Submitted:	19 Feb 2009 18:37	Modified:	18 Dec 2009 13:20
Reporter:	Sergey Petrunya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
As the following EXPLAIN shows, FORCE INDEX hint is ignored when join buffering is used

mysql> explain select * from ten A force  index for order by (A) , t2 B where A.a < 4 order by A.a;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | A     | range | a             | a    | 5       | NULL |    8 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | B     | ALL   | NULL          | NULL | NULL    | NULL |   30 | Using join buffer                            | 
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
2 rows in set (0.01 sec)

How to repeat:
1. Load the database dump (will upload here)
2. Run the above mentioned EXPLAIN.

Test dataset

Attachment: bug43029.sql (, text), 16.12 KiB.

Thank you for the report.

Verified as described.

In version 6.0 EXPLAIN is different:

explain select * from ten A force  index for order by (A) , t2 B where A.a < 4 order by A.a;
id      1
select_type     SIMPLE
table   A
type    range
possible_keys   a
key     a
key_len 5
ref     NULL
rows    8
Extra   Using index condition; Using MRR; Using temporary; Using filesort
id      1
select_type     SIMPLE
table   B
type    ALL
possible_keys   NULL
key     NULL
key_len NULL
ref     NULL
rows    30
Extra   Using join buffer

Just to clarify: If the hint was properly honred, the EXPLAIN would produce the same join order and access methods, but
1. The second table won't use join buffer.
1.  Which would allow not to use filesort (as the index we're using for the first table produces suitable ordering).

that is, the right EXPLAIN should be without "Using join bufffer" and  "Using temporary; Using filesort"

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

3147 Georgi Kodinov	2009-09-30
      Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join 
      buffering is used
      
      Made FORCE INDEX FOR ORDER BY prevent the optimizer from 
      using join cache and sorting instead of the whole result instead of
      sorting over the first table.

Review sent by email.

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

3147 Georgi Kodinov	2009-10-07
      Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join 
      buffering is used
      
      FORCE INDEX FOR ORDER BY now prevents the optimizer from 
      using join buffering. As a result the optimizer can use
      indexed access on the first table and doesn't need to 
      sort the complete resultset at the end of the statement.

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

3149 Georgi Kodinov	2009-10-07
      Bug #43029: FORCE INDEX FOR ORDER BY is ignored when join 
      buffering is used
      
      FORCE INDEX FOR ORDER BY now prevents the optimizer from 
      using join buffering. As a result the optimizer can use
      indexed access on the first table and doesn't need to 
      sort the complete resultset at the end of the statement.

Pushed into 5.1.41 (revid:joro@sun.com-20091014143611-cphb0enjlx6lpat1) (version source revid:satya.bn@sun.com-20091013071829-zc4c3go44j6re592) (merge vers: 5.1.40) (pib:13)

Noted in 5.1.41 changelog.

The FORCE INDEX FOR ORDER BY index hint was ignored when join 
buffering was used.  

Setting report to NDI pending push into 5.5.x+.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091019131708-bc6pv55x6287a0wc) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.5.0, 6.0.14 changelogs.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

Already noted in earlier changelogs.