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

Indexes are not used/Cardinality for index is NULL in TokuDB

    XMLWordPrintable

    Details

    • Sprint:
      10.0.26

      Description

      Hi,
      after migration to tokudb from Innodb/XtraDB (mysqldump) or creattion new databases (default_storage_engine=TokuDB) we noticed that some queries run very slowly especially when we compared it to the same queries performad on InnoDB.

      After analysis of query execution we observed that the indexes are not used or used other than with InnoDB.
      This is cause by the fact that the Cardinality for index was always NULL (except PRIMARY)

      show index from

      +-----------------------------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table                       | Non_unique | Key_name                | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +-----------------------------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | log_link_visit_action |          0 | PRIMARY                 |            1 | idlink_va        | A         |  3033501310 |     NULL | NULL   |      | BTREE      |         |               |     
      | log_link_visit_action |          1 | index_idvisit           |            1 | idvisit          | A         |  NULL |     NULL | NULL   |      | BTREE      |         |               |     
      | log_link_visit_action |          1 | index_idsite_servertime |            1 | idsite           | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |     
      | log_link_visit_action |          1 | index_idsite_servertime |            2 | server_time      | A         |  NULL |     NULL | NULL   |      | BTREE      |         |               |     
      | log_link_visit_action |          1 | transitions_url         |            1 | idaction_url     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |     
      | log_link_visit_action |          1 | transitions_url         |            2 | idsite           | A         |      NULL |     NULL | NULL   |      | BTREE      |         |               |     
      | log_link_visit_action |          1 | transitions_url         |            3 | server_time      | A         |  NULL |     NULL | NULL   |      | BTREE      |         |               |     
      | log_link_visit_action |          1 | transitions_url_ref     |            1 | idaction_url_ref | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |     
      | log_link_visit_action |          1 | transitions_url_ref     |            2 | idsite           | A         |      NULL |     NULL | NULL   |      | BTREE      |         |               |     
      | log_link_visit_action |          1 | transitions_url_ref     |            3 | server_time      | A         |  NULL |     NULL | NULL   |      | BTREE      |         |               |     
      +-----------------------------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      

      Run the ANALYZE TABLE command solved the problem and index cardinality began to be updated automatically.

      Should it not be done automatically out of the box?
      Is there any way to avoid performing manual ANALYZE TABLE

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                jlubzinski Jacek Lubzinski
              • Votes:
                1 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: