Menu

MySQL Indexes

Learn how MySQL indexes support lookups, and how to create, drop, inspect, and evaluate indexes with query plans.

Simply put, the index is equivalent to table of contents in the dictionary, which can locate the content you want to find faster.

If there is no index, when you execute a query, MySQL will scan the entire table row by row and return the rows that meet the criteria. This is not a problem if the table is small. If the table contains many rows, say a million or more, a full table scan will be a slow process. The use of indexes can greatly speed up the speed of MySQL queries.

You can create multiple indexes, as in a dictionary, either by letter or by word length. In MySQL, you can create an index on a single column, or on multiple columns.

The implementation of the index can use different data structures, such as B-Tree, hash, etc. Some data and information pointing to the actual physical address of the data are stored in the index, so the index requires a certain amount of storage space.

Indexes will slow down the speed of inserting, modifying, and deleting operation because these operations cause changes to the index. For example, when inserting data, MySQL need to index the new data.

Learn how to create indexes, drop indexes, inspect indexes, and design composite indexes and unique indexes. Use MySQL EXPLAIN and EXPLAIN ANALYZE to inspect estimated plans and actual query execution.

For equivalent index-inspection commands in other database engines, see list indexes across SQL databases.

Advertisement

  1. MySQL SHOW INDEXES

    Learn to use MySQL SHOW INDEXES to inspect index names, columns, uniqueness, and visibility, including when troubleshooting Error 1061.

  2. MySQL DROP INDEX

    Learn to drop a MySQL index with DROP INDEX, inspect remaining indexes, and understand online DDL behavior and primary-key risks.

  3. MySQL CREATE INDEX

    Learn MySQL CREATE INDEX syntax, choose indexed columns, create unique indexes, inspect query plans, and resolve Error 1061 for duplicate index names.

  4. MySQL EXPLAIN and EXPLAIN ANALYZE: Read Query Plans

    Use MySQL EXPLAIN for estimated plans and EXPLAIN ANALYZE for actual rows, timing, and loops; learn when ANALYZE executes the statement.

  5. MySQL UNIQUE INDEX

    Learn how MySQL unique indexes prevent duplicate values, define composite keys, and diagnose duplicate-key errors such as Error 1062.

  6. MySQL USE INDEX

    This article describes how to use the USE INDEX to recommend MySQL query optimizer use specified named indexes.

  7. MySQL Composite Indexes

    This article describes composite indexes in MySQL, that is, indexes built on multiple columns.

  8. MySQL Clustered Indexes

    This article describes clustered indexes in MySQL and how to manage clustered indexes in InnoDB tables.

  9. MySQL index cardinality

    This article discusses the index cardinality of MySQL and how to use the SHOW INDEXES command to view index cardinality.

  10. MySQL Invisible Indexes

    This article discusses MySQL invisible indexes and the common usage.

  11. MySQL Prefix Indexes

    This article discusses how to create prefix indexes for string columns in MySQL.

  12. MySQL Index Order

    This article discusses how to use ascending and descending indexes in MySQL to improve query performance.