O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required