When using the pt-online-schema-change tool, can the indexes of the original table be preserved?

Question

To avoid having to "warm-up" the new table, are the old indexes kept around?

Answer 1

Yes, all indexes are included in the definition of the new table.

This is easy to test:

mysql> create table test.sometable 
  ( x int primary key, y int, z int, unique key (y), key (z,y));

Then try to change the table with pt-online-schema-change:

$ pt-online-schema-change D=test,t=sometable --alter "ENGINE=InnoDB" --execute

Now inspect the table to make sure it still has the indexes it started with:

mysql> SHOW CREATE TABLE test.sometable\G

However, your question mentions "warm-up" which is a phrase often associated with having pages from the table in the buffer pool. As the ALTER makes changes to pages, they must be in the buffer pool on their way to the disk. So by the end of the ALTER, you do have a lot of the pages in the buffer pool.

You can test this with the new INFORMATION_SCHEMA tables for InnoDB metrics that are present in MySQL 5.5 and 5.6:

mysql> SELECT TABLE_NAME, SPACE, COUNT(*) AS num_pages
  FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU
  WHERE TABLE_NAME = '`mydatabase`.`mytable`'
  GROUP BY TABLE_NAME, SPACE;
-- Result shows a lot of pages loaded

mysql> ALTER TABLE mydatabase.mytable ENGINE=INNODB; -- dummy table restucture

mysql> SELECT TABLE_NAME, SPACE, COUNT(*) AS num_pages
  FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU
  WHERE TABLE_NAME = '`mydatabase`.`mytable`'
  GROUP BY TABLE_NAME, SPACE;
-- Result shows a lot of pages still loaded, but the space id has changed

But in cases when the table is larger than the capacity of the buffer pool, you end up with a subset of pages, and whether this subset is optimal for a warmed-up buffer pool for that table is not guaranteed.

The same thing happens when you use pt-online-schema-change. The tablespace id changes, but there are a lot of pages in the buffer pool following an alter.