| Bug #18144 | Cost with FORCE/USE index seems incorrect in some cases. | ||
|---|---|---|---|
| Submitted: | 10 Mar 2006 21:56 | Modified: | 29 Sep 18:07 |
| Reporter: | Tobias Asplund | ||
| Status: | Patch approved | ||
| Category: | Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.21-BK, 5.0.18 | OS: | Linux (Linux, FreeBSD) |
| Assigned to: | Martin Hansson | Target Version: | |
| Triage: | Triaged: D3 (Medium) | ||
[4 Apr 2006 16:34]
Valeriy Kravchuk
Thank you for a problem report. I was able to repeat the behaviour you described on
5.0.21-BK build on Linux:
mysql> CREATE INDEX Continent ON Country (Continent);
Query OK, 239 rows affected (0.04 sec)
Records: 239 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT Continent, SUM(Population) AS Pop FROM Country USE INDEX
(Continent) GROUP BY Continent\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Country
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 239
Extra: Using temporary; Using filesort
1 row in set (0.00 sec)
mysql> show status like '%cost';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| Last_query_cost | 304.028248 |
+-----------------+------------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT Continent, SUM(Population) AS Pop FROM Country FORCE INDE
X (Continent) GROUP BY Continent\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Country
type: index
possible_keys: NULL
key: Continent
key_len: 1
ref: NULL
rows: 239
Extra:
1 row in set (0.00 sec)
mysql> show status like '%cost'; SUM(Population) AS Po
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| Last_query_cost | 304.028248 |
+-----------------+------------+
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.21 |
+-----------+
1 row in set (0.00 sec)
But (!) in first case you have to read all table rows, 239, then sort and put into the
temporary tables only those columns. In the second case you have to read all index
records (similar to temporary table rows in size) AND all table rows, 239, as your index
has no Population column. So, why do you think the cost should be different?
[5 Apr 2006 15:11]
Tobias Asplund
Since the operations made in those two queries are so fundamentally different, them ending up at the same cost seems very coinscidental, which is what made me think in the first place that there may be a bug for this and uses the same number for both. If the cost would have been close, but not exactly the same I would have been fine with it, though. But if it actually takes the exact same amount of resources to run both, I'll defer.
[5 Apr 2006 16:47]
Tobias Asplund
Ok, let's assume that it actually has the same cost. Why doesn't the optimizer let me run it the way I want when I say USE INDEX - if the cost would be basically the same, it should let me override the "default" plan. The other option is that when forced it doesn't calculate a cost...
[31 Jul 2007 14:24]
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/31894 ChangeSet@1.2485, 2007-07-31 14:24:00+02:00, mhansson@linux-st28.site +4 -0 Bug#18144: Cost with FORCE/USE index seems incorrect in some cases. Most of the times FORCE INDEX is ignored when the plan would be more expensive than table scan. In GROUP BY cases, however, the cost calculation disregarded the extra cost of doing table scan by using a non-covering index. Fixed by setting the cost to #rows + #rows * cost of evaluating the where clause in this case.
[10 Sep 2007 17:03]
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/33997 ChangeSet@1.2485, 2007-09-10 17:04:43+02:00, mhansson@linux-st28.site +4 -0 Bug#18144: Cost with FORCE/USE index seems incorrect in some cases. Most of the times FORCE INDEX is ignored when the plan would be more expensive than table scan. In GROUP BY cases, however, it is not, but the cost-based optimizer used the same cost for performing a table scan by using a non-covering index. Fixed by setting the cost to the same as reading a single range the size of the table.
[13 Sep 2007 16:39]
Martin Hansson
Timour, when you review this, please consider the applicability for 5.0. Joro is skeptical.
[25 Sep 11:23]
Martin Hansson
Requesting re-triage as this bug is quite old.
Description: eeyore> EXPLAIN SELECT Continent, SUM(Population) AS Pop FROM Country USE INDEX (Continent) GROUP BY Continent \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Country type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 239 Extra: Using temporary; Using filesort 1 row in set (0.00 sec) eeyore> SHOW STATUS LIKE '%cost'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | Last_query_cost | 304.086598 | +-----------------+------------+ 1 row in set (0.00 sec) eeyore> EXPLAIN SELECT Continent, SUM(Population) AS Pop FROM Country FORCE INDEX (Continent) GROUP BY Continent \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Country type: index possible_keys: NULL key: Continent key_len: 1 ref: NULL rows: 239 Extra: 1 row in set (0.00 sec) eeyore> SHOW STATUS LIKE '%cost'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | Last_query_cost | 304.086598 | +-----------------+------------+ 1 row in set (0.00 sec) It seems that those two getting the exact same cost seems a bit weird, it feels like one should be more expensive than the other in this case. How to repeat: Install the world example database from http://downloads.mysql.com/docs/world.sql Once that is installed, run: CREATE INDEX Continent ON Country (Continent); Then run the queries above.