3366
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3365
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3364
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3363
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3362
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3361
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3360
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3359
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3358
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3357
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3356
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3355
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3354
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3353
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3352
|
|
|
Daniël van Eeden |
12 years ago
|
|
|
3351
|
|
BUG#11882131: OPTIMIZER CHOOSES FILESORT WHEN REVERSE INDEX SCAN COULD BE USED
Consider the following case:
CREATE TABLE t1 (a INT,KEY (a)); INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
This query could have been resolved by GROUP range access if it hadn't been for the descending ordering [1].
To access this table, covering index scan is first chosen. Later an attempt to avoid sorting is made by calling test_if_skip_sort_order(). Range analysis now decides that GROUP range access is the most efficient access method, but since this access method cannot produce records in descending order, it is scrapped by test_if_skip_sort_order() before concluding that filesort is required after all.
In this case, test_if_skip_sort_order() fails to check if the descending ordering can be resolved by scanning the covering index in reverse order instead. Because of this, the resulting execution plan is to 1) scan the index and 2) sort the result instead of simply do 1) scan the index in reverse order.
This patch adds an interesting_order parameter to test_quick_select(). This parameter ensures that only range access plans that can produce rows in requested order are considered.
The gains from this change include: 1) Optimizer will not spend time to calculate whether or not an unusable range access plan is cheap. 2) Before, if two range access plans P1 and P2 were considered, and P1 could produce the requested ordering but P2 could not, P2 would still be returned from test_quick_select() if it was cheaper than P1. test_if_skip_sort_order() would then discard the range access plan as not usable. With this patch, P2 will not be considered, so test_quick_select() will instead return the best *usable* plan P1. 3) Due to #2, the aforementioned deficiency in test_if_skip_sort_order() is no longer an issue: If test_quick_select() returns a range access plan, that plan will be able to resolve the requested ordering.
|
Jorgen Loland |
12 years ago
|
|
|
3350
|
|
|
Mattias Jonsson |
12 years ago
|
|
|
3349
|
|
|
Magne Mahre |
12 years ago
|
|
|
3348
|
|
|
Dmitry Lenev |
12 years ago
|
|
|
3347
|
|
|
Sergey Glukhov |
12 years ago
|
|
|