Rebuild index on InnoDB

Question

I have Large table with 2 million rows and 50 columns.
When iam updating/inserting large amount of data iam dropping all indexes and rebuild them again using 2 queries. this works fine

But iam thinking to use another query for that example :

ALTER TABLE [table_name]  ENGINE = InnoDB

as i read from the mysql guide here http://dev.mysql.com/doc/refman/5.6/en/optimize-table.html

InnoDB Details

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the output of OPTIMIZE TABLE when you run it on an InnoDB table

Also i think optimize will rebuild the index?

 OPTIMIZE TABLE [table_name] 

what do you recommend, my table have more then 2 million rows and 50 columns

Solution

Have you been doing SHOW TABLE STATUS before and after your drop+rebuild? Does Index_length change much? Probably not by even a factor of two.

I almost never recommend rebuilding anything in InnoDB. It's not worth it. A glaring exception has to do with with FULLTEXT indexes.

Yes the dummy ALTER will rebuild the indexes. So will OPTIMIZE TABLE. Both will "defragment" (to some extent) the secondary index BTrees and the main BTree (which contains the data and PRIMARY KEY).

The statistics can be much more cheaply updated using just ANALYZE TABLE. Even that is not often needed. 5.6 has a much better way of maintaining the stats.

If you are not already using innodb_file_per_table=ON, I suggest you set that (SET GLOBAL ...) and do ALTER TABLE tbl ENGINE=InnoDB; one last time.

Online alter

To change ft_*, you need to rebuild the index. This implies an ALTER (or OPTIMIZE, which is implemented as ALTER). Newer versions of MySQL have ALGORITHM=INPLACE which makes ALTER have little or no impact on the running system. But, there are limitations. Check the manual.

An alternative to a non-INPLACE ALTER is pt-query-digest or gh-ost. See if either of them will work for your case.

Short of "rebuilding the table", you can DROP INDEX ... and ADD INDEX .... Again, I don't know if these work for FT indexes "inplace". Anyway, you would lose the use of that index during the process.

Comments

[Joseph Idziorek]

Can you please restructure your question to ask a specific technical question as opposed to a recommendation?

[user207421]

I doubt it is even necessary to drop and rebuild the indexes, unless you have too many of them. Do you have some concrete evidence to the contrary?

[Deckard]

I have changed innodb_ft_min_token_size and need to rebuild the fulltext indexes. I think I might need to optimize table.... And I have to do it right now...I wish I had some time waiting for your answer

[Rick James]

The OPTIMIZE won't do anything more useful than rebuilding the indexes. (I noted FULLTEXT as an exception.)

[Deckard]

While doing optimize table..., the tabled is locked so I had to interrupt the query.. it has about 2,000,000 rows.. Could you give any advice?

[Rick James]

If you change ft..., you have to rebuild the index. Read the manual on ALTER to see if this is one of the "inplace" cases. (I doubt if it is.)

[Rick James]

I added a discussion of online alter options.