MySQL Bugs: #25764: Query Cost Estimate seems to only cover a part of subqueries, not full query.

Bug #25764	Query Cost Estimate seems to only cover a part of subqueries, not full query.
Submitted:	22 Jan 2007 20:59	Modified:	25 Jan 2009 17:06
Reporter:	Tobias Asplund	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.36-BK, 6.0.8-bzr	OS:	Linux (Linux, Mac OS X)
Assigned to:		CPU Architecture:	Any

Description:
In the case of this nested subquery:

mysql> SELECT DISTINCT Country.Name FROM Country INNER JOIN CountryLanguage AS CL ON CL.CountryCode = Country.Code   WHERE CL.Language IN (     SELECT Language FROM CountryLanguage WHERE CountryCode = (SELECT Code FROM Country WHERE Name = 'Finland')) AND   Country.Name   <> 'Finland';
+------------------------+
| Name                   |
+------------------------+
| Azerbaijan             | 
| Georgia                | 
| Svalbard and Jan Mayen |
..........................
| Russian Federation     | 
| Estonia                | 
+------------------------+
19 rows in set (0.06 sec)

mysql> show session status like '%cost%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.199000 | 
+-----------------+----------+
1 row in set (0.00 sec)

The cost seems way too low, so let's look at this:

mysql> EXPLAIN SELECT DISTINCT Country.Name FROM Country INNER JOIN CountryLanguage AS CL ON CL.CountryCode = Country.Code   WHERE CL.Language IN (     SELECT Language FROM CountryLanguage WHERE CountryCode = (SELECT Code FROM Country WHERE Name = 'Finland')) AND   Country.Name   <> 'Finland';
+----+--------------------+-----------------+--------+---------------+---------+---------+--------------------+------+------------------------------------+
| id | select_type        | table           | type   | possible_keys | key     | key_len | ref                | rows | Extra                              |
+----+--------------------+-----------------+--------+---------------+---------+---------+--------------------+------+------------------------------------+
|  1 | PRIMARY            | Country         | ALL    | PRIMARY       | NULL    | NULL    | NULL               |  239 | Using where; Using temporary       | 
|  1 | PRIMARY            | CL              | ref    | PRIMARY       | PRIMARY | 3       | world.Country.Code |    9 | Using where; Using index; Distinct | 
|  2 | DEPENDENT SUBQUERY | CountryLanguage | eq_ref | PRIMARY       | PRIMARY | 33      | const,func         |    1 | Using where; Using index           | 
|  3 | SUBQUERY           | Country         | ALL    | NULL          | NULL    | NULL    | NULL               |  239 | Using where                        | 
+----+--------------------+-----------------+--------+---------------+---------+---------+--------------------+------+------------------------------------+
4 rows in set (0.00 sec)

Let's compare the cost to the cost of only the first part of the explain output; the scan of Country:

mysql> SELECT * FROM Country;
+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+-----------------------------------+---------+-------+
| Code | Name                                         | Continent     | Region                    | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP        | GNPOld     | LocalName                                    | GovernmentForm                               | HeadOfState                       | Capital | Code2 |
+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+-----------------------------------+---------+-------+
| AFG  | Afghanistan                                  | Asia          | Southern and Central Asia |   652090.00 |      1919 |   22720000 |           45.9 |    5976.00 |       NULL | Afganistan/Afqanestan                        | Islamic Emirate                              | Mohammad Omar                     |       1 | AF    |
.........................
.........................

| UMI  | United States Minor Outlying Islands         | Oceania       | Micronesia/Caribbean      |       16.00 |      NULL |          0 |           NULL |       0.00 |       NULL | United States Minor Outlying Islands         | Dependent Territory of the US                | George W. Bush                    |    NULL | UM    | 
+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+-----------------------------------+---------+-------+
239 rows in set (0.64 sec)

mysql> show session status like '%cost%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 65.028248 | 
+-----------------+-----------+
1 row in set (0.00 sec)

So, only the first part of the query is 30X more expensive than the full query?
It seems like the cost somehow only references a part of the full query at the top here...

How to repeat:
Install the world example database from mysql.com manual pages.

See queries above.

Thank you for a bug report. Verified just as described with latest 5.0.36-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot world
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT DISTINCT Country.Name FROM Country INNER JOIN CountryLanguage AS
    -> CL ON CL.CountryCode = Country.Code   WHERE CL.Language IN (     SELECT
Language
    -> FROM CountryLanguage WHERE CountryCode = (SELECT Code FROM Country WHERE
 Name =
    -> 'Finland')) AND   Country.Name   <> 'Finland';
+------------------------+
| Name                   |
+------------------------+
| Azerbaijan             |
...
| Russian Federation     |
| Estonia                |
+------------------------+
19 rows in set (0.05 sec)

mysql>  show session status like '%cost%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 1.199000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> explain SELECT DISTINCT Country.Name FROM Country INNER JOIN CountryLang
uage AS CL ON CL.CountryCode = Country.Code   WHERE CL.Language IN (     SELECT
 Language FROM CountryLanguage WHERE CountryCode = (SELECT Code FROM Country WH
ERE Name = 'Finland')) AND   Country.Name   <> 'Finland'\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: Country
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 239
        Extra: Using where; Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: CL
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: world.Country.Code
         rows: 9
        Extra: Using where; Using index; Distinct
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: CountryLanguage
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 33
          ref: const,func
         rows: 1
        Extra: Using where; Using index
*************************** 4. row ***************************
           id: 3
  select_type: SUBQUERY
        table: Country
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 239
        Extra: Using where
4 rows in set (0.01 sec)

mysql> explain SELECT * FROM Country\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:
1 row in set (0.01 sec)

mysql>  show session status like '%cost%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 65.028248 |
+-----------------+-----------+
1 row in set (0.00 sec)

This will be fixed maily later, in 5.2, together with introducing  subquery optimizations.

The bug is still repeatable with latest 6.0.8 from bzr, but now query cost for the entire query is just 0.000000.

The 0.00000 cost was intentional, see BUG#30377

Hi! We've discussed this problem and see that 
- this property has been introduced intentionally (see bug reference in the previous comment). 
- we can't agree on whether it's better to have incomplete cost or no cost

that is, we could agree that 
- on one hand, lack of correct cost is a problem in general (that's why I'm not closing this bug)
- on the other hand, current behavior is what we have as an interim solution (and thus is ok to have in [several] subsequent versions).

Considering the above, we ask you to remove any 6.0 tags. This might be fixed post-6.0.

Moving back to Verified, as I'm not actively working on this atm.