Home > MariaDB, MySQL > MariaDB MySQL Percona list all indexes without using INFORMATION_SCHEMA.STATISTICS

MariaDB MySQL Percona list all indexes without using INFORMATION_SCHEMA.STATISTICS

There is nothing more to be said:

SELECT
gen.TABLE_SCHEMA
, gen.TABLE_NAME
, (select
count(TABLE_NAME) from information_schema.columns idx
where
idx.TABLE_SCHEMA = gen.TABLE_SCHEMA
and idx.TABLE_NAME=gen.TABLE_NAME
) as COLUMN_NUM
, (select
count(TABLE_NAME) from information_schema.columns idx
where
idx.TABLE_SCHEMA = gen.TABLE_SCHEMA
and idx.TABLE_NAME=gen.TABLE_NAME
and COLUMN_KEY != ""
) as INDEX_NUM_ALL
, (select
count(TABLE_NAME) from information_schema.columns idx
where
idx.TABLE_SCHEMA = gen.TABLE_SCHEMA
and idx.TABLE_NAME=gen.TABLE_NAME
and COLUMN_KEY = "PRI"
) as INDEX_NUM_PRI
, (select
count(TABLE_NAME) from information_schema.columns idx
where
idx.TABLE_SCHEMA = gen.TABLE_SCHEMA
and idx.TABLE_NAME=gen.TABLE_NAME
and COLUMN_KEY = "UNI"
) as INDEX_NUM_UNI
, (select
count(TABLE_NAME) from information_schema.columns idx
where
idx.TABLE_SCHEMA = gen.TABLE_SCHEMA
and idx.TABLE_NAME=gen.TABLE_NAME
and COLUMN_KEY = "MUL"
) as INDEX_NUM_MUL

from information_schema.tables gen
where true
and gen.TABLE_SCHEMA !='mysql'
and gen.TABLE_SCHEMA!='performance_schema'
and gen.TABLE_SCHEMA!='information_schema'
;
+-----------------+-----------------------+------------+---------------+---------------+---------------+---------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NUM | INDEX_NUM_ALL | INDEX_NUM_PRI | INDEX_NUM_UNI | INDEX_NUM_MUL |
+-----------------+-----------------------+------------+---------------+---------------+---------------+---------------+
Advertisements
Categories: MariaDB, MySQL Tags: , , ,
  1. July 9, 2015 at 6:02 am

    What is the point here? You are opening and closing the columns view multiple times when you could open statistics once. What is the advantage of not using the statistics table?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: