We're updating the issue view to help you get more done. 

TokuDB not using index for specific queries on percona server

Description

example query and explain output

1 2 3 4 5 6 explain SELECT log_link_visit_action.idvisit, log_link_visit_action.server_time FROM piwiklog_link_visit_action AS log_link_visit_action WHERE log_link_visit_action.idsite in ('1') AND log_link_visit_action.server_time >= '2016-07-31 19:00:00' AND log_link_visit_action.server_time < '2016-08-01 19:00:00'; +----+-------------+-----------------------+-------+-------------------------+-------------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------------+-------+-------------------------+-------------------------+---------+------+------+-------------+ | 1 | SIMPLE | log_link_visit_action | range | index_idsite_servertime | index_idsite_servertime | 9 | NULL | 1 | Using where | +----+-------------+-----------------------+-------+-------------------------+-------------------------+---------+------+------+-------------+

show index from

1 2 3 4 5 6 7 8 +----------------------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------------------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | piwiklog_link_visit_action | 0 | PRIMARY | 1 | idlink_va | A | 1177488 | NULL | NULL | | BTREE | | | | piwiklog_link_visit_action | 1 | index_idvisit | 1 | idvisit | A | 1177488 | NULL | NULL | | BTREE | | | | piwiklog_link_visit_action | 1 | index_idsite_servertime | 1 | idsite | A | 1177488 | NULL | NULL | | BTREE | | | | piwiklog_link_visit_action | 1 | index_idsite_servertime | 2 | server_time | A | 1177488 | NULL | NULL | | BTREE | | | +----------------------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

show create table

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 CREATE TABLE `piwiklog_link_visit_action` ( `idlink_va` int(11) unsigned NOT NULL AUTO_INCREMENT, `idsite` int(10) unsigned NOT NULL, `idvisitor` binary(8) NOT NULL, `server_time` datetime NOT NULL, `idvisit` int(10) unsigned NOT NULL, `idaction_url` int(10) unsigned DEFAULT NULL, `idaction_url_ref` int(10) unsigned DEFAULT '0', `idaction_name` int(10) unsigned DEFAULT NULL, `idaction_name_ref` int(10) unsigned NOT NULL, `idaction_event_category` int(10) unsigned DEFAULT NULL, `idaction_event_action` int(10) unsigned DEFAULT NULL, `time_spent_ref_action` int(10) unsigned NOT NULL, `custom_var_k1` varchar(200) DEFAULT NULL, `custom_var_v1` varchar(200) DEFAULT NULL, `custom_var_k2` varchar(200) DEFAULT NULL, `custom_var_v2` varchar(200) DEFAULT NULL, `custom_var_k3` varchar(200) DEFAULT NULL, `custom_var_v3` varchar(200) DEFAULT NULL, `custom_var_k4` varchar(200) DEFAULT NULL, `custom_var_v4` varchar(200) DEFAULT NULL, `custom_var_k5` varchar(200) DEFAULT NULL, `custom_var_v5` varchar(200) DEFAULT NULL, `custom_float` float DEFAULT NULL, `idaction_content_interaction` int(10) unsigned DEFAULT NULL, `idaction_content_name` int(10) unsigned DEFAULT NULL, `idaction_content_piece` int(10) unsigned DEFAULT NULL, `idaction_content_target` int(10) unsigned DEFAULT NULL, `time_spent` int(10) unsigned DEFAULT NULL, `custom_dimension_1` varchar(255) DEFAULT NULL, `custom_dimension_2` varchar(255) DEFAULT NULL, `custom_dimension_3` varchar(255) DEFAULT NULL, `custom_dimension_4` varchar(255) DEFAULT NULL, `custom_dimension_5` varchar(255) DEFAULT NULL, `custom_dimension_6` varchar(255) DEFAULT NULL, PRIMARY KEY (`idlink_va`), KEY `index_idvisit` (`idvisit`), KEY `index_idsite_servertime` (`idsite`,`server_time`) ) ENGINE=TokuDB AUTO_INCREMENT=2342854 DEFAULT CHARSET=latin1

After change engine to innodb query works as expected

example query and explain output with tokudb

1 2 3 4 5 6 explain SELECT log_link_visit_action.idvisit, log_link_visit_action.server_time FROM piwiklog_link_visit_action AS log_link_visit_action WHERE log_link_visit_action.idsite in ('1') AND log_link_visit_action.server_time >= '2016-07-31 19:00:00' AND log_link_visit_action.server_time < '2016-08-01 19:00:00'; +----+-------------+-----------------------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+ | 1 | SIMPLE | log_link_visit_action | range | index_idsite_servertime | index_idsite_servertime | 9 | NULL | 1 | Using index condition | +----+-------------+-----------------------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+

I test this mostly on percona server (different versions) but similar issue occurred also on mysql and mariadb.

Environment

None

Status

Assignee

George Lorch

Reporter

Jacek Lubzinski

Labels

External issue ID

None

External issue ID

None

External issue ID

None

External issue ID

None

External issue ID

None

External issue ID

None

External issue ID

None

External issue ID

None

External issue ID

None

Freshdesk Tickets

None

Affects versions

Percona-Server-5.6.31-77.0
Percona-Server-5.7.13-6

Priority

Major