[MDEV-9163] Indexes are not used/Cardinality for index is NULL in TokuDB Created: 2015-11-22  Updated: 2016-06-22  Resolved: 2016-06-22

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - TokuDB
Affects Version/s: 10.0, 10.1
Fix Version/s: 10.0.26, 10.1.15

Type: Bug Priority: Major
Reporter: Jacek Lubzinski Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: tokudb, upstream-fixed

Issue Links:
Blocks
is blocked by MDEV-9537 10.0.24 merge Closed
PartOf
is part of MDEV-10224 10.0.26 merge Closed
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

 Comments   
Comment by Elena Stepanova [ 2015-11-23 ]

Apparently it was fixed in TokuDB few days ago: https://tokutek.atlassian.net/browse/DB-306 .

serg, could you please take a look to see if it will be automatically fixed by the TokuDB merge, or does it also require any changes on the server side?

Comment by Sergei Golubchik [ 2016-02-17 ]

It will be automatically merged when we get a new TokuDB.
Unfortunately, TokuDB is currently unusable. When TokuDB will be fixed, I'll push it into the main tree and this bug will go away.

Generated at Thu Feb 08 07:32:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.