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.

Answer 1

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.

Answer 2

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