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:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.21-BK, 5.0.18 OS:Linux (Linux, FreeBSD)
Assigned to: Martin Hansson
Triage: Triaged: D3 (Medium)

[10 Mar 2006 20:56] Tobias Asplund
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.
[4 Apr 2006 14:34] Valerii 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.