MySQL 8 Cookbook

Adding indexes

Without an index, MySQL must scan the entire table row by row to find the relevant rows. If the table has an index on the columns that you are filtering for, MySQL can quickly find the rows in the big data file without scanning the whole file.

MySQL can use an index for filtering of rows in WHERE, ORDER BY, and GROUP BY clauses, and also for joining tables. If there are multiple indexes on a column, MySQL chooses the index that gives maximum filtering of rows.

You can execute the ALTER TABLE command to add or drop the index. Both index addition and dropping are online operations and do not hinder the DMLs on the table, but they take lot of time on larger tables.