Make use of InnoDB fast index creation in mysqldump, ALTER TABLE and OPTIMIZE TABLE

Bug #744103 reported by Alexey Kopytov on 2011-03-28
14
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
Undecided
Alexey Kopytov
5.1
Fix Released
Undecided
Alexey Kopytov
5.5
Fix Released
Undecided
Alexey Kopytov

Bug Description

Currently, InnoDB fast index creation can only be used when explicitly creating or dropping indexes.

We can make use of that optimization in mysqldump and ALTER TABLE / OPTIMIZE TABLE. Namely:

1. mysqldump should be updated to dump InnoDB tables so that secondary indexes are created after loading the data.

2. When ALTER TABLE requires a table copy, it can optimize copying the data by temporarily dropping secondary keys before copying and recreating them after it.

3. Since OPTIMIZE TABLE is performed as ALTER TABLE internally, it will benefit from the same optimization as well.

References:

http://www.mysqlperformanceblog.com/2010/12/09/thinking-about-running-optimize-on-your-innodb-table-stop/
http://bugs.mysql.com/bug.php?id=49120
http://bugs.mysql.com/bug.php?id=57583

Tags: fr Edit Tag help

Related branches

tags: added: fr
Alexey Kopytov (akopytov) wrote :

Released in 5.1.56-12.7 and 5.5.11-20.2.

Alexey Kopytov (akopytov) wrote :

Adding a remote bug watch:

http://bugs.mysql.com/bug.php?id=49120

Alexey Kopytov (akopytov) wrote :

Adding a remote bug watch:

http://bugs.mysql.com/bug.php?id=57583

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2619

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.