MySQL Indexes

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.

Here, you can learn MySQL indexes through a series of tutorials, including creating indexes, dropping indexes, listing all indexes of a table, and the advantages and disadvantages of using indexes in MySQL.

  1. MySQL SHOW INDEXES

    In MySQL, you can use the SHOW INDEXES command to all indexes from a table.

  2. MySQL DROP INDEX

    In a MySQL database, you can drop an existing index from a table usding the DROP INDEX statement.

  3. MySQL CREATE INDEX

    This article descrbes how to create indexes in MySQL using the CREATE INDEX statement.

  4. MySQL UNIQUE INDEX

    A MySQL unique index is a special index that not only speeds up data retrieval from a table, but also prevents duplicate values ​​from appearing in one or more specified columns.

  5. MySQL USE INDEX

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

  6. MySQL Composite Indexes

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

  7. MySQL Clustered Indexes

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

  8. MySQL index cardinality

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

  9. MySQL Invisible Indexes

    This article discusses MySQL invisible indexes and the common usage.

  10. MySQL Prefix Indexes

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

  11. MySQL Index Order

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