Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0, 10.1
-
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
