Bug #3942 EXPLAIN returns incorect table name

• Submitted: 31 May 2004 19:12
• Modified: 1 Jun 2004 16:46
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.18
• OS: Linux (Linux 2.4.2x)
• CPU Architecture: Any

[31 May 2004 19:12] [ name withheld ]

Description:
after renaming column name from areg to arid using alter table, 
explain shows that i have areg, but not arid. 
example is below. (table type is TYPE=MyISAM)

mysql> explain select arid from apmokejimas;
+-------------+-------+---------------+------+---------+------+------+-------------+
| table       | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+-------------+-------+---------------+------+---------+------+------+-------------+
| apmokejimas | index | NULL          | areg |       5 | NULL |   68 | Using index |
+-------------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> describe apmokejimas;
+--------+----------------------+------+-----+----------------+----------------+
| Field  | Type                 | Null | Key | Default        | Extra          |
+--------+----------------------+------+-----+----------------+----------------+
| aid    | int(10) unsigned     |      | PRI | NULL           | auto_increment |
| arid   | int(10) unsigned     |      | MUL | 0              |                |
+--------+----------------------+------+-----+----------------+----------------+
2 rows in set (0.00 sec)

mysql> select arid from apmokejimas limit 1;
+------+
| arid |
+------+
| 1365 |
+------+
1 row in set (0.01 sec)

mysql> 

How to repeat:
create table with column name name1;
rename column name from name1 to name2 using alter table;
use explain to see bug:
mysql> explain select name2 from table;

and you will see that name1 is on output but no name2.

Suggested fix:
:)

[31 May 2004 21:25] [ name withheld ]

seems like that old key name isn't changend when key name is changing. This causes  sometimes confution for beginer user.

[1 Jun 2004 16:46] Matthew Lord

What you are noticing is that when you do not provide a name for the index a default index name is 
used.  That default comes from the column name, if there is already an index with that name is 
appends _2 and so on.  You are seeing the index name in the explain output not the column name.  
Issue a show keys from table\G and you will see the column the index is on.  To avoid any confusion it's 
always best to name your indexes but then again you may want to change the name of the index if 
changing the name of the column.  Currently you would have to drop the index and re-create it.  If you 
do both alterations in one alter table statement you will not incur additional alter time.