How to rebuild indexes in an existing table after adding index in MySQL?

Question

I have a table with 50k rows in MySQL and recently added a new index. When I run (which shows the size of each index in my table):

SELECT database_name, table_name, index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE table_name = 'table_name' and stat_name = 'size' 
ORDER BY size_in_mb DESC;

it shows 1.5 mb for my newly created index. Does this mean that MySQL automatically indexes the existing table when I create a new index or do I have to reindex it manually with OPTIMIZE TABLE or similar commands? I am using InnoDB (version 5.7.26).

Speed wise, it doesn't seem to improve the performance of my queries at all, that's why I was thinking that reindexing is necessary.

score 2 · Answer 1 · answered Oct 17 '20 at 13:15

2

Creating an index also builds the index. Index creation data definition language is considered online DDL.

answered Oct 17 '20 at 13:15

O. Jones

103,626
17
118
172

score 1 · Accepted Answer · answered Oct 17 '20 at 13:14

Does this mean that MySQL automatically indexes the existing table when I create a new index?

Yes, it does. It scans the existing data in the table and creates the index accordingly. If you are creating a unique index, it also ensures that the data satisfies the unicity contraint, and fails if it doesn't.

Do I have to reindex it manually with OPTIMIZE TABLE?

The newly created index probably won't benefit an immediate optimize table. It might still be a good idea to do it, because the command reorganizes the storage of the table itself, and of other indexes if any.

How to rebuild indexes in an existing table after adding index in MySQL?

2 Answers2