Bug #81347 unnecessary scanned all databases for information_schema
Submitted: 8 May 2016 16:10 Modified: 13 Oct 2016 16:16
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.73, 5.5.49, 5.6.30,5.7.12 OS:CentOS
Assigned to: CPU Architecture:Any

[8 May 2016 16:10] Dave Pullin
Description:
explain shows unnecessary 'scanned all databases' for information_schema.
And the execution of the query confirms that MySQL is scanning on databases, because it is a very slow process.
It is unnecessary (I think) because the there is no reason to look in any database other than the named one to find or determine the absence of the named table. (as shown below when the left join is changed to join).

mysql> explain select * from (select version() ) as constants
    -> left join INFORMATION_SCHEMA.tables on table_schema='mysql' and table_name='proc' and table_catalog is null;
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------------------------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra                                  |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------------------------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |                                        |
|  1 | PRIMARY     | tables     | ALL    | NULL          | NULL | NULL    | NULL | NULL | Open_full_table; Scanned all databases |
|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used                         |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------------------------------+
3 rows in set (0.00 sec)

explain select * from (select version() ) as constants
    -> join INFORMATION_SCHEMA.tables on table_schema='mysql' and table_name='proc' and table_catalog is null;
+----+-------------+------------+--------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
| id | select_type | table      | type   | possible_keys | key                     | key_len | ref  | rows | Extra                                             |
+----+-------------+------------+--------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL                    | NULL    | NULL |    1 |                                                   |
|  1 | PRIMARY     | tables     | ALL    | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL | Using where; Open_full_table; Scanned 0 databases |
|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL                    | NULL    | NULL | NULL | No tables used                                    |
+----+-------------+------------+--------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)

How to repeat:
explain select * from (select version() ) as constants 
left join INFORMATION_SCHEMA.tables on table_schema='mysql' and table_name='proc' and table_catalog is null;
[10 May 2016 13:01] MySQL Verification Team
Hello Dave Pullin,

Thank you for the report and test case.
Observed this with 5.5.49, 5.6.30 and 5.7.12 builds.

Thanks,
Umesh
[13 Oct 2016 16:16] Paul DuBois
Posted by developer:
 
Noted in 8.0.0 changelog.

Queries on INFORMATION_SCHEMA tables that resulted in directory scans
to determine database or file names no longer do so, but instead read
database and table names from the data dictionary.