Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

LP #744103: Make use of InnoDB fast index creation in mysqldump, ALTER TABLE and OPTIMIZE TABLE

Description

**Reported in Launchpad by Alexey Kopytov last update 22-05-2011 06:05:46

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

Environment

None

Smart Checklist

Activity

Show:

lpjirasync January 23, 2018 at 5:10 PM

**Comment from Launchpad by: Alexey Kopytov on: 22-05-2011 06:05:45

Adding a remote bug watch:

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

lpjirasync January 23, 2018 at 5:10 PM

**Comment from Launchpad by: Alexey Kopytov on: 22-05-2011 06:05:31

Adding a remote bug watch:

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

lpjirasync January 23, 2018 at 5:10 PM

**Comment from Launchpad by: Alexey Kopytov on: 22-05-2011 06:05:02

Released in 5.1.56-12.7 and 5.5.11-20.2.

Done

Details

Assignee

Reporter

Labels

Priority

Smart Checklist

Created January 23, 2018 at 5:10 PM
Updated January 23, 2018 at 5:10 PM
Resolved January 23, 2018 at 5:10 PM

Flag notifications