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
Triage: Triaged: D3 (Medium)

[19 Feb 2009 18:37] Sergey Petrunya
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.
[19 Feb 2009 18:40] Sergey Petrunya
Test dataset

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

[20 Feb 2009 7:19] Sveta Smirnova
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
[20 Feb 2009 11:59] Sergey Petrunya
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"
[30 Sep 2009 13:39] 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/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.
[7 Oct 2009 11:29] Martin Hansson
Review sent by email.
[7 Oct 2009 15:04] 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/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.
[8 Oct 2009 8:41] 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/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.
[14 Oct 2009 14:40] Bugs System
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)
[15 Oct 2009 23:57] Paul Dubois
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+.
[22 Oct 2009 6:36] Bugs System
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)
[22 Oct 2009 7:08] Bugs System
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)
[22 Oct 2009 19:40] Paul Dubois
Noted in 5.5.0, 6.0.14 changelogs.
[18 Dec 2009 10:37] Bugs System
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)
[18 Dec 2009 10:52] Bugs System
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)
[18 Dec 2009 11:07] Bugs System
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)
[18 Dec 2009 11:21] Bugs System
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)
[18 Dec 2009 13:20] MC Brown
Already noted in earlier changelogs.