MySQL Bugs: #2294: SHOW INDEX is not symetrical with EXPLAIN

Bug #2294	SHOW INDEX is not symetrical with EXPLAIN
Submitted:	6 Jan 2004 5:50	Modified:	9 Jan 2006 14:41
Reporter:	Ed Soniat	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.0.17-standard	OS:	Linux (Linux Red Hat 8.0)
Assigned to:		CPU Architecture:	Any

Description:
Show index should show the length of each element consistent with EXPLAIN making it easier to determine what components of the key are being applied.

How to repeat:
Create a table with a multi column index with column types of varying width and NULL status.

use SHOW INDEX FROM <table> to show the index from your table.  Note that no information is provided about the key length for each column.

Populate the table such that EXPLAIN will show a full utilization of the index. 
See my comment under EXPLAIN.

Note that the explain will describe the key length but there is not way to directly relate this to the SHOW INDEX FROM data

Suggested fix:
Add a column to the SHOW INDEX FROM <table> that shows the byte offset of each column in the index or total length consistent with EXPLAIN.

Thank you for a feature request. Sorry, but I can not get your point: if indexed column has a variable width, what size you want to report about in SHOW INDEX? Maximum? Then it is redundant information, because you have column names for each indexe and you can easily get maximum column width.

Show index shows the columns in an index without the size.
Explian indicates the size of the portion of the index used but not the columns.
To know from explain which columns are used you must use calcuate the size of
each component and deduce from the sum of the sizes which columns explain has indicated are in use when it tells you the size.  There would be greater utility if a show index and an explain would contian complete and consistent information that would quickly allow a user to ascertain which columns explain is indicating are being used in the select.

If this still isn't clear I will be glad to discuss this over the phone or in any way that will help explain my suggestion.

Here is an show index and an explain.

mysql> show index from CiEvent;
+---------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| CiEvent |          1 | reporting  |            1 | opportunityId | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| CiEvent |          1 | reporting  |            2 | EVENT         | A         |        NULL |       15 | NULL   |      | BTREE      |         |
| CiEvent |          1 | reporting  |            3 | timestamp     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| CiEvent |          1 | reporting  |            4 | vendor_oid    | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| CiEvent |          1 | reporting2 |            1 | EVENT         | A         |        NULL |       15 | NULL   |      | BTREE      |         |
| CiEvent |          1 | reporting2 |            2 | timestamp     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| CiEvent |          1 | reporting2 |            3 | vendor_oid    | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| CiEvent |          1 | analytics  |            1 | visitorId     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| CiEvent |          1 | etl        |            1 | timestamp     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+---------+------------+------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
9 rows in set (0.00 sec)

mysql> explain select * from CiEvent where opportunityId=0 and EVENT='foo';
+----+-------------+---------+------+----------------------+-----------+---------+-------------+------+-------------+
| id | select_type | table   | type | possible_keys        | key       | key_len | ref         | rows | Extra       |
+----+-------------+---------+------+----------------------+-----------+---------+-------------+------+-------------+
|  1 | SIMPLE      | CiEvent | ref  | reporting,reporting2 | reporting |      24 | const,const |    1 | Using where |
+----+-------------+---------+------+----------------------+-----------+---------+-------------+------+-------------+
1 row in set (0.05 sec)

mysql>

The first column(s) from index (prefix) are always used. Do you really think that these:

mysql> explain select * from CiEvent where opportunityId=0 and EVENT='foo';
+----+-------------+---------+------+----------------------+-----------+--------
-+-------------+------+-------------+
| id | select_type | table   | type | possible_keys        | key       | key_len
| ref         | rows | Extra       |
+----+-------------+---------+------+----------------------+-----------+--------
-+-------------+------+-------------+
|  1 | SIMPLE      | CiEvent | ref  | reporting,reporting2 | reporting |      24
| const,const |    1 | Using where |
+----+-------------+---------+------+----------------------+-----------+--------
-+-------------+------+-------------+
1 row in set (0.05 sec)

access type (ref) and extra comment (Using where) is not enough to decide anything you want about index usage? Just execute "desc CiEvent", if you do not remember your columns, and you got all the information.

Can you, please, provide an example of the information you want from some other database?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".