MySQL Indexes
Check
There are multiple ways to retrieve the same index information...
SHOW INDEXES FROM myTable;
SHOW INDEXES IN myTable;
SHOW INDEXES FROM myTable IN myDB;
SHOW INDEXES IN myTable FROM myDB;
SHOW INDEXES FROM myDB.myTable;
SHOW INDEXES IN myDB.myTable;
SHOW INDEXES FROM myTable WHERE ...
SHOW INDEX FROM myTable;
SHOW INDEX IN myTable;
SHOW INDEX FROM myTable IN myDB;
SHOW INDEX IN myTable IN myDB;
SHOW INDEX FROM myDB.myTable;
SHOW INDEX IN myDB.myTable;
SHOW INDEX FROM myTable WHERE ...
SHOW KEYS FROM myTable;
SHOW KEYS IN myTable;
SHOW KEYS FROM myTable IN myDB;
SHOW KEYS IN myTable IN myDB;
SHOW KEYS FROM myDB.myTable;
SHOW KEYS IN myDB.myTable;
SHOW KEYS FROM myTable WHERE ...
TABLE
NON_UNIQUE
KEY_NAME
SEQ_IN_INDEX
COLUMN_NAME
COLLATION
CARDINALITY
SUB_PART
PACKED
NULL
INDEX_TYPE
COMMENT
INDEX_COMMENT
VISIBLE
EXPRESSION
Table Name
1 = Duplicates allowed; 0 = No Duplicates NOT allowed
Index Name. Primary Key is always PRIMARY
Shows order of columns in a multi-column index
Column Name (NULL for expression based indexes)
A = Ascending; B = Descending; NULL = not sorted
An estimated number of unique values in the index
NULL if entire column is indexed otherwise shows number of indexed characters for partial column index
NULL if not Packed
YES = NULLs allowed; NULL = NULLs NOT allowed
BTREE, HASH, RTREE, FULLTEXT
COMMENT attribute from index creation
YES = Visible to Query Optimizer; NO = Invisible to Query Optimizer
Expression used for expression based indexes
CREATE
ALTER TABLE mytable ADD INDEX(mycolumn);
or...
CREATE INDEX myindex ON mytable(mycolumn);
DROP
DROP INDEX myindex ON mytable;
Unused Indexes
To enable statistics collection...
SET GLOBAL userstat=1;
By default, statistics are not collected.SELECT *
FROM INFORMATION_SCHEMA.TABLE_STATISTICS;
SELECT *
FROM INDEX_STATISTICS;
Report Unused Indexes...
SELECT DISTINCT s.TABLE_SCHEMA,
s.TABLE_NAME,
s.INDEX_NAME
FROM information_schema.statistics `s`
LEFT JOIN information_schema.index_statistics INDXS ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA
AND s.TABLE_NAME=INDXS.TABLE_NAME
AND s.INDEX_NAME=INDXS.INDEX_NAME)
WHERE INDXS.TABLE_SCHEMA IS NULL;
Defragmentation
Performing an online rebuild of a table will rebuild (and thus defragment) the associated indexes....
ALTER TABLE mytable ENGINE=INNODB;
- ALTER TABLE mytable FORCE; is a common alternative that does the same thing.
- The rebuild is an online operation.
- You can achieve the same thing by using mysqldump to dump, drop, and reload the table (but this will require an outage).