Bug #31446 | Inefficient index use with order by and joins | ||
---|---|---|---|
Submitted: | 8 Oct 2007 12:14 | Modified: | 16 Oct 2008 14:44 |
Reporter: | Pekka Lund | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
Version: | 5.0.44, 5.0.37, 6.0.2 Alpha | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[8 Oct 2007 12:14]
Pekka Lund
[28 Oct 2007 13:41]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described. At least, manual should have a note that this optimization does not work if JOIN is involved.
[26 Jun 2008 22:39]
Sergey Petrunya
> At least, manual should have a note that this optimization does not work if JOIN is involved. This is not true. The optimization may work if join is involved in certain cases.
[27 Jun 2008 9:16]
Sergey Petrunya
The use of filesort is needed in the non-indexed case because of use of join buffering. On recent MySQL 5.1 (from about a year ago or later), use of join buffering is indicated in EXPLAIN: mysql> EXPLAIN SELECT t1.a FROM t1 JOIN t2 ON t1.a=t2.a WHERE t1.a=1 ORDER BY t1.b LIMIT 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ref possible_keys: t1_ab key: t1_ab key_len: 4 ref: const rows: 956 Extra: Using index; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1996 Extra: Using where; Using join buffer 2 rows in set (0.01 sec) (an explanation of what join buffering is available here: http://s.petrunia.net/blog/?p=18) At the moment the optimizer always choses to do join buffering + filesort over doing no join buffering and no filesort (DUMB-CHOICE). It is not possible to affect this choice. For MySQL 6.1, we have WL#4421 "Add hints on join buffer usage for join queries" which will allow the user to force or disable join buffering. We consider the [DUMB-CHOICE] to be a feature request bug. The optimizer ought to do a cost-based choice between join buffer + filesort and no join buffer and no filesort. We intend to fix this in MySQL 6.x (x=0,1,...).
[25 Nov 2008 16:28]
Omer Barnir
triage: correcting target to 6.0 (checked bug)