getting specific columns from sql statement

Question

TO show indexes we use the following query,

show indexes from student;
+---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY               |            1 | ROLL_NO     | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | stu_roll_no_age_index |            1 | ROLL_NO     | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | stu_roll_no_age_index |            2 | AGE         | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+--------------+

Now I am trying to select only Table from the above as follows,

select Table, Key_name, Column_name,Index_type from (show indexes from student);

I think I might be wrong but I am not getting how to reason it? Please tell me. And also , how do I get only specific columns from the result of such query.

You can not do this. Try Sysindexes rather.

Ankit Bajpai
– Ankit Bajpai

2018-09-12 13:12:33 +00:00
Commented Sep 12, 2018 at 13:12 — Ankit Bajpai
– Ankit Bajpai, Commented Sep 12, 2018 at 13:12

cn0047 · Accepted Answer · 2018-09-12 13:13:54Z

1

You can use table STATISTICS from db information_schema, like this:

select TABLE_NAME, INDEX_NAME, COLUMN_NAME, INDEX_TYPE
from information_schema.STATISTICS WHERE TABLE_NAME = 'student';

answered Sep 12, 2018 at 13:13

cn0047

17.2k7 gold badges61 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anil Over a year ago

Nice one.. It worked. :).. Can you please tell me what is wrong with my approach?

cn0047 Over a year ago

Your approach wasn't wrong, it's just impossible to select something from show index query.

Collectives™ on Stack Overflow

getting specific columns from sql statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related