When it comes to MySQL, you need to search the INFORMATION_SCHEMA database.
The following tables will allow you to hunt down a column through all your indexes.
TABLE DESCRIPTIONS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
mysql> show create table information_schema.key_column_usage\G
*************************** 1. row ***************************
Table: KEY_COLUMN_USAGE
Create Table: CREATE TEMPORARY TABLE `KEY_COLUMN_USAGE` (
`CONSTRAINT_CATALOG` varchar(512) NOT NULL DEFAULT '',
`CONSTRAINT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`CONSTRAINT_NAME` varchar(64) NOT NULL DEFAULT '',
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
`ORDINAL_POSITION` bigint(10) NOT NULL DEFAULT '0',
`POSITION_IN_UNIQUE_CONSTRAINT` bigint(10) DEFAULT NULL,
`REFERENCED_TABLE_SCHEMA` varchar(64) DEFAULT NULL,
`REFERENCED_TABLE_NAME` varchar(64) DEFAULT NULL,
`REFERENCED_COLUMN_NAME` varchar(64) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
INFORMATION_SCHEMA.STATISTICS
mysql> show create table information_schema.statistics\G
*************************** 1. row ***************************
Table: STATISTICS
Create Table: CREATE TEMPORARY TABLE `STATISTICS` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`NON_UNIQUE` bigint(1) NOT NULL DEFAULT '0',
`INDEX_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`INDEX_NAME` varchar(64) NOT NULL DEFAULT '',
`SEQ_IN_INDEX` bigint(2) NOT NULL DEFAULT '0',
`COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
`COLLATION` varchar(1) DEFAULT NULL,
`CARDINALITY` bigint(21) DEFAULT NULL,
`SUB_PART` bigint(3) DEFAULT NULL,
`PACKED` varchar(10) DEFAULT NULL,
`NULLABLE` varchar(3) NOT NULL DEFAULT '',
`INDEX_TYPE` varchar(16) NOT NULL DEFAULT '',
`COMMENT` varchar(16) DEFAULT NULL,
`INDEX_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
For example, you can search for the indexes with a column Column_Name_I_Want
like this:
EXAMPLE #1
SELECT table_schema DB,table_name TBL,
constraint_name NDX,
seq_in_index NDX_POS,position_in_unique_constraint CONST_POS
FROM information_schema.key_column_usage WHERE column_name = 'Column_Name_I_Want'
AND table_schema NOT IN ('information_schema','mysql','performance_schema');
EXAMPLE #2
SELECT
table_schema DB,table_name TBL,
index_name NDX,seq_in_index NDXPOS
FROM information_schema.statistics WHERE column_name = 'Column_Name_I_Want'
AND table_schema NOT IN ('information_schema','mysql','performance_schema');
CAVEAT
Please note that these types of queries will be slow because MySQL's INFORMATION_SCHEMA
- are MEMORY table without indexes
- are slow to access InnoDB
- with lots of tables
- when opening file handles just to read metadata
- with InnoDB tables accessing its metadata
- are slow to access partitioned MyISAM tables with hundreds of partitions
- are fast to access non-partitioned MyISAM tables