Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-2619

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Low
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      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

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist