CODEWITHSUNDEEP
mysqldatabaseindexinginnodb
inventor
inventor

Reputation: 55

Rebuild index on InnoDB

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

Upvotes: 1

Views: 18097

Answers (1)

Rick James
Rick James

Reputation: 142296

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.

Upvotes: 6

Related Questions