How do I index pre-existing data in MySQL?

Question

I created an index on a MySQL innodb table that I imported data too. The table was created by MySQL when the table was imported. I did nothing. It simply imported the data with the GUI.

Then I tried to add an index:

ALTER TABLE my_table ADD INDEX idx_myindex (some_column);

I always get:

Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0

I read this question:

MySql Add Index : 0 Rows Affected

How do I make my table that already has data in it to index the column which actually affects the row? I am using innoDB.

Do I need to export, delete all data, reimport (making index update)?

Answer 1

MySQL 5.1 and earlier reported N rows affected when you create an index, and N was always the number of rows in the table.

Starting in MySQL 5.5.5, they stopped reporting that number, and decided that rows affected was always zero for all DDL statements. See http://bugs.mysql.com/bug.php?id=21818

Be assured that creating an index automatically populates it with values. You can verify this by running SHOW TABLE STATUS LIKE 'mytable' before and after creating the index. One of the fields shown is index_length, which increases after you add a secondary index.