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);

Index creation will be online where possible but may resort to a blocking (copy and switch) approach if necessary. 

To force an online rebuild (and fail with an error if it's not possible)...

CREATE INDEX myindex ON mytable(mycolumn) ALGORITHM = INPLACE;

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).

Bibliography