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)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.