MySQL Bugs: #24693: explain output should provide un-aliased table names

Bug #24693	explain output should provide un-aliased table names
Submitted:	29 Nov 2006 13:26	Modified:	3 Oct 2008 12:41
Reporter:	Anders Henke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When using EXPLAIN on a query with aliased table names, explain shows the aliases instead of the "real" table names. This can be useful for queries who reference the same table twice with different names, but it's also quite a hazzle to distinguish the exact patterns, table names and their matchings "on your own".

Aliases are often used not necessarily to make it easier to read the SQL statement but easier to write the frontend code; e.g. something like
   "select * from ${prefix}_bar bar, ${prefix}_foo foo where ..." 
is quite often used in today's applications in order to enable a table name prefix, but still reference to the same (non-prefixed) names within the WHERE statement.

So if you're running EXPLAIN on such queries in order to find (and possibly add) missing indexes, you've to manually "parse" the query in your own mind in order to get the "real" table names, so that you can run "add index" or "alter table" on the correct table.

How to repeat:
mysql> create temporary table foo (bar varchar(255));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into foo values(1);
Query OK, 1 row affected (0.00 sec)

mysql> explain select * from foo where bar=1;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | foo   | system | NULL          | NULL |    NULL | NULL |    1 |       | 
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from foo f where bar=1;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | f     | system | NULL          | NULL |    NULL | NULL |    1 |       | 
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
1 row in set (0.01 sec)

The table is shown as "f" instead of "foo".

Suggested fix:
I'd like to be able to also get the "real" table name from the EXPLAIN command, e.g. as an own column (preferred ...) or using an option to EXPLAIN, e.g. "real" or "noalias" might give either only the non-aliased table names or the non-aliased names in brackets:

mysql> explain real select from foo f where bar=1

+----+-------------+--------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | f (foo) | system | NULL          | NULL |    NULL | NULL |    1 |       | 
+----+-------------+--------+--------+---------------+------+---------+------+------+-------+

mysql> explain noalias select * from foo where bar=1;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | foo   | system | NULL          | NULL |    NULL | NULL |    1 |       | 
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+

Thank you for a feature request. I agree that option to see real table names instead of aliases (or with aliases) may be useful in some cases.

I would like to second the desire to have this feature. Let me go into a little detail.

Because MySQL doesn't supply a method for knowing how often an index or table is used in the DB, we do this:

(1) Get a list of all queries submitted to a slave,
(2) Do EXPLAIN on all those queries (millions),
(3) Analyze the explain to figure out what tables/indexes are used,
(4) Drop unneeded tables/indexes from the database.

Step 3 is not guaranteed to be correct, because MySQL returns table aliases instead of actual names. So I think I can drop the "users" table because it's never references in any of our millions of queries. In fact, it's been aliased as "usr" or somesuch in every query (this is a contrived example, actual problems are usually much more subtle than this).

I have somewhat solved that problem by doing some SQL parsing with the script that does (2), but it is not guaranteed to catch all cases, and again, with millions of queries involved, the SQL parsing is a significant amount of CPU power of the machine running the EXPLAIN script.

I guess for my purposes, if MySQL supplied table/index usage stats, I wouldn't care so much about this feature anymore, but it is possible in the future I would care for some other reason (again probably involving automatic analysis of EXPLAINs).

Would this feature request really be very hard to implement and/or backport to MySQL 5.0+?

Sorry for posting two comments, but the script to do the EXPLAINs to help with index analysis is open source, and you can view it here:

http://www.faemalia.net/mysqlUtils/getServerIndexes.pl

The code that does the simplistic SQL parsing to attempt to determine actual table names from aliases is this:

                        if (length ($possibleAliasName) <= 3) {
                            if ($queryInQuestion =~ / (\w+) AS|as $possibleAliasName /) {
                                $actualTableName = $1;
                            } elsif ($queryInQuestion =~ /, (\w+) $possibleAliasName /) {
                                $actualTableName = $1;
                            } elsif ($queryInQuestion =~ /(from|FROM) (\w+) $possibleAliasName /) {
                                $actualTableName = $2;
                            } elsif ($queryInQuestion =~ /(join|JOIN) (\w+) $possibleAliasName /) {
                                $actualTableName = $2;
                            }
                        }

Which I'm 100% positive misses a few cases, or catches cases that don't make any sense (for example, if the developer uses mixed-case to spell Join or From, or if they use a table alias that is >3 characters in length).

Any progress on that?

Why this is marked as "Triaged: D5 (Feature request)" and not a bug?
The docs: http://dev.mysql.com/doc/refman/5.6/en/explain-output.html#explain_table
clearly says "The name of the table to which the row of output refers".
"The name of the table", not "The alias of the table".

Also the same problem is in new JSON format.

Please fix it.
Also I'd love to be a part of any initiative taken to fix this one.

Show both the real and the aliased table names in JSON explain