| Bug #69367 | EXPLAIN shows "Scanned 1 database" while the query scans many databases | ||
|---|---|---|---|
| Submitted: | 31 May 2013 20:32 | Modified: | 31 May 2013 22:55 |
| Reporter: | Sergey Petrunya | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[31 May 2013 20:42]
Sergey Petrunya
fixed synopsis
[31 May 2013 22:55]
MySQL Verification Team
Thank you for the bug report.
[7 Jun 2013 9:46]
Georgi Kodinov
Posted by developer:
The problem is in the way EXPLAIN prints the extra column. In explain_extra() function there's the following code :
if (table_list->has_db_lookup_value &&
table_list->has_table_lookup_value)
{
if (push_extra(ET_SCANNED_DATABASES, "0"))
return true;
}
else if (table_list->has_db_lookup_value ||
table_list->has_table_lookup_value)
{
if (push_extra(ET_SCANNED_DATABASES, "1"))
return true;
}
else
{
if (push_extra(ET_SCANNED_DATABASES, "all"))
return true;
}
The first branch is correct : if there's both table and database lookup it won't scan any databases.
But the second branch is bad : if there's a table lookup value it may scan more than 1 database as you're correctly pointing out.
Description: For some queries over I_S tables, EXPLAIN will show "Scanned 1 database" while the query actually scans many databases. Look at the example below. The query looks for table t1 in all databases. Query result returns 8 tables in different databases. However, EXPLAIN shows "Scanned 1 database". How to repeat: MySQL [test]> create database test1; Query OK, 1 row affected (0.00 sec) MySQL [test]> create database test2; Query OK, 1 row affected (0.00 sec) MySQL [test]> create table test1.t1 (a int); Query OK, 0 rows affected (0.26 sec) MySQL [test]> create table test2.t1 (a int); Query OK, 0 rows affected (0.27 sec) MySQL [test]> explain select table_schema, table_name from information_schema.tables where table_name='t1'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tables type: ALL possible_keys: NULL key: TABLE_NAME key_len: NULL ref: NULL rows: NULL Extra: Using where; Skip_open_table; Scanned 1 database 1 row in set (4.96 sec) MySQL [test]> select table_schema, table_name from information_schema.tables where table_name='t1'; +--------------+------------+ | table_schema | table_name | +--------------+------------+ ... | test | t1 | | test1 | t1 | | test2 | t1 | +--------------+------------+ 8 rows in set (0.03 sec)