| Bug #18144 | Cost with FORCE/USE index seems incorrect in some cases. | ||
|---|---|---|---|
| Submitted: | 10 Mar 2006 20:56 | Modified: | 13 Aug 2010 2:23 |
| Reporter: | Tobias Asplund | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.21-BK, 5.0.18 | OS: | Linux (Linux, FreeBSD) |
| Assigned to: | Martin Hansson | CPU Architecture: | Any |
[4 Apr 2006 14: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 13: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 14: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 12: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 15: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 14:39]
Martin Hansson
Timour, when you review this, please consider the applicability for 5.0. Joro is skeptical.
[25 Sep 2009 9:23]
Martin Hansson
Requesting re-triage as this bug is quite old.
[12 Dec 2009 6:27]
Jeetendra Ranjan
Hi, After analysing slow query log i found that some queries are not using index and so i used the force index command in query and test it and now it starts using index properly.Accordingly i implemented the same query with force index in my application code and regeneratet the slow query log. Now i found that the same queries having force index clause are againg not using index and surprisingly its starting using index without any force index clause. Please suggest how it happened and should i continue with the force index command in that query or remove the force index clause from those queries ? Thanks & Regards Jeetendra
[25 May 2010 14:59]
Martin Hansson
Can I have a target version, please?
[24 Jun 2010 12:17]
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/112076 3269 Martin Hansson 2010-06-24 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.
[23 Jul 2010 12:35]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[4 Aug 2010 8:07]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[4 Aug 2010 8:23]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[13 Aug 2010 2:23]
Paul DuBois
Noted in 5.6.0 changelog. For queries with GROUP BY, FORCE INDEX was not ignored as it should have been when it would result in a more expensive query plan.
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.