Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4400

wrong numbers for SHOW INDEX and SHOW TABLE STATUS with EITS

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.0.2, 10.0, 10.1
    • 10.1
    • Admin statements, Optimizer
    • observed in the 10.0-mdev83 tree used for DBT3

    Description

      With EITS (use_stat_tables='preferably') and correct stats in mysql.table/index_stats the following statements return the InnoDB internal estimates instead of the exact numbers:

      • SHOW TABLE STATUS
      • SHOW INDEX FROM ...

      Example results for a table with 8000 rows:

      +----------+
      | count(*) |
      +----------+
      |     8000 |
      +----------+
      +---------+---------+----------+----------+
      | Table   | Op      | Msg_type | Msg_text |
      +---------+---------+----------+----------+
      | test.t1 | analyze | status   | OK       |
      +---------+---------+----------+----------+
      +---------+------------+-------------+
      | db_name | table_name | cardinality |
      +---------+------------+-------------+
      | test    | t1         |        8000 |
      +---------+------------+-------------+
      +---------+------------+------------+--------------+---------------+
      | db_name | table_name | index_name | prefix_arity | avg_frequency |
      +---------+------------+------------+--------------+---------------+
      | test    | t1         | PRIMARY    |            1 |        1.0000 |
      | test    | t1         | c1         |            1 |        1.0000 |
      | test    | t1         | c2         |            1 |       81.6327 |
      +---------+------------+------------+--------------+---------------+
      +----------------+
      | c2 cardinality |
      +----------------+
      |        97.9999 |
      +----------------+
      +-------+----------+--------------+-------------+-------------+
      | Table | Key_name | Seq_in_index | Column_name | Cardinality |
      +-------+----------+--------------+-------------+-------------+
      | t1    | PRIMARY  |            1 | c1          |        8050 |
      | t1    | c1       |            1 | c1          |        8050 |
      | t1    | c2       |            1 | c2          |         187 |
      +-------+----------+--------------+-------------+-------------+
      +------+--------+---------+------------+------+
      | Name | Engine | Version | Row_format | Rows |
      +------+--------+---------+------------+------+
      | t1   | InnoDB |      10 | Compact    | 8050 |
      +------+--------+---------+------------+------+

      Attachments

        Activity

          People

            Unassigned Unassigned
            axel Axel Schwenke
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.