Archive

Posts Tagged ‘indexes’

MariaDB MySQL Percona list all indexes without using INFORMATION_SCHEMA.STATISTICS

July 8, 2015 1 comment

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: , , ,