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

Statistics for InnoDB table is wrong if persistent statistics is used

    Details

      Description

      When persistent statistics is used for InnoDB it still updates cardinality with values which are incorrect. Basically, upstream bugs:

      https://bugs.mysql.com/bug.php?id=80178
      https://jira.percona.com/browse/PS-1693

      apply to MariaDB as well. This is a result from the test case on MariaDB 10.3.7, for example:

      ...
      MariaDB [test]> select count(distinct customer_id), count(distinct sale_time), c
      ount(distinct product_id) from sale;
      +-----------------------------+---------------------------+----------------------------+
      | count(distinct customer_id) | count(distinct sale_time) | count(distinct product_id) |
      +-----------------------------+---------------------------+----------------------------+
      |                           3 |                         5 |     6 |
      +-----------------------------+---------------------------+----------------------------+
      1 row in set (0.005 sec)
       
      MariaDB [test]> select count(*), count(distinct customer_id), count(distinct cus
      tomer_id, sale_time), count(distinct product_id) from sale;
      +----------+-----------------------------+----------------------------------------+----------------------------+
      | count(*) | count(distinct customer_id) | count(distinct customer_id, sale_time) | count(distinct product_id) |
      +----------+-----------------------------+----------------------------------------+----------------------------+
      |      694 |                           3 |                                     15 |                          6 |
      +----------+-----------------------------+--------------------------------------
      --+----------------------------+
      1 row in set (0.005 sec)
       
      MariaDB [test]> analyze table sale;
      +-----------+---------+----------+----------+
      | Table     | Op      | Msg_type | Msg_text |
      +-----------+---------+----------+----------+
      | test.sale | analyze | status   | OK       |
      +-----------+---------+----------+----------+
      1 row in set (0.133 sec)
       
      MariaDB [test]> show indexes from sale;
      +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | sale  |          0 | PRIMARY     |            1 | id          | A         |      694 |     NULL | NULL   |      | BTREE      |         |               |
      | sale  |          1 | customer_id |            1 | customer_id | A         |        6 |     NULL | NULL   |      | BTREE      |         |               |
      | sale  |          1 | customer_id |            2 | sale_time   | A         |       30 |     NULL | NULL   |      | BTREE      |         |               |
      | sale  |          1 | product_id  |            1 | product_id  | A         |       12 |     NULL | NULL   |      | BTREE      |         |               |
      +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      4 rows in set (0.007 sec)
       
      MariaDB [test]> show create table sale\G
      *************************** 1. row ***************************
             Table: sale
      Create Table: CREATE TABLE `sale` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `customer_id` int(11) NOT NULL,
        `product_id` int(11) NOT NULL,
        `sale_time` datetime NOT NULL,
        `sale_value` decimal(10,2) NOT NULL,
        `filler` varchar(250) NOT NULL,
        PRIMARY KEY (`id`),
        KEY `customer_id` (`customer_id`,`sale_time`),
        KEY `product_id` (`product_id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=887 DEFAULT CHARSET=latin1 STATS_AUTO_RECALC=0 ST
      ATS_SAMPLE_PAGES=1000
      1 row in set (0.016 sec)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                valerii Valerii Kravchuk
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: