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;
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;