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

LP #1201802: Wrong cardinality estimate on a re-created table after a fully-scanning ANALYZE TABLE

    XMLWordPrintable

    Details

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

      Description

      **Reported in Launchpad by Laurynas Biveinis last update 16-07-2013 14:39:31

      If a table is created, populated, and dropped, and then recreated and re-populated, the index cardinality estimates will differ, even after innodb_stats_sample_pages to a large value and running explicit ANALYZE TABLE. This is a Percona Server-specific bug.

      --source include/have_innodb.inc

      SET @@GLOBAL.innodb_stats_sample_pages=30000;
      SET @@GLOBAL.innodb_stats_on_metadata=OFF;

      --disable_warnings
      DROP TABLE IF EXISTS t1;
      --enable_warnings

      CREATE TABLE t1(id INT NOT NULL PRIMARY KEY, data TEXT) ENGINE=InnoDB;

      INSERT INTO t1 VALUES(1, '');
      INSERT INTO t1 VALUES(2, '');

      DELETE FROM t1 WHERE id = 2;

      ANALYZE TABLE t1;

      SHOW INDEXES IN t1; # Cardinality == 1

      DROP TABLE t1;

      CREATE TABLE t1(id INT NOT NULL PRIMARY KEY, data TEXT) ENGINE=InnoDB;

      INSERT INTO t1 VALUES(1, '');
      INSERT INTO t1 VALUES(2, '');

      DELETE FROM t1 WHERE id = 2;

      ANALYZE TABLE t1;

      SHOW INDEXES IN t1; # Cardinality == 2

      DROP TABLE t1;

      SET @@GLOBAL.innodb_stats_sample_pages=default;
      SET @@GLOBAL.innodb_stats_on_metadata=default;

        Smart Checklist

          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: