Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
5.5.38, 10.0(EOL), 10.1(EOL)
-
Ubuntu 14.04.4 LTS (GNU/Linux 4.2.0-27-generic x86_64)
MariaDB version : 10.0.23-MariaDB
tokudb_version: 5.6.26-74.0
Description
Hello,
We sometime face problems with poorly chosen index for queries on TokuDB tables.
It can lead to important performance problems.
This issue does not happen on recent Percona Server versions (we tried Percona 5.6.29 or 5.7.11).
Please find attached a dataset.
It consists in 4 tables, including toku_problem_myisam and toku_problem_toku, which are the same tables with a different engine (MyISAM for one and TokuDB for the other). The two other tables are in MyISAM
We first execute, using the MyISAM table (toku_problem_myisam)
EXPLAIN
|
SELECT * |
FROM ( |
SELECT dt psdate,d,h,so,mo,anneeo, code_elt_sql,type_elt, dn |
FROM |
toku_problem_tempologie
|
INNER JOIN toku_problem_topo |
WHERE LENGTH(dn) IN (39) AND dn REGEXP '0118151|0118152|0118153|0118154|0118155|0118156' AND dn LIKE '0000001,0106486%' AND type_elt ='cellule' AND (dt BETWEEN '2015-06-02 00:00:00' AND '2015-06-16 23:59:59') |
) AS sys |
LEFT JOIN toku_problem_myisam ON ((sys.d = toku_problem_myisam.date_debut_mesure AND sys.h = toku_problem_myisam.heure_debut_mesure) AND (sys.code_elt_sql = toku_problem_myisam.ni)) |
GROUP BY |
LEFT(sys.dn,39),3; |
+------+-------------+-------------------------+------+-----------------------------+----------+---------+------------------------------------------------------------------------------------------------------------------------------+------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------------------------+------+-----------------------------+----------+---------+------------------------------------------------------------------------------------------------------------------------------+------+--------------------------------------------------------+
|
| 1 | SIMPLE | toku_problem_topo | ref | dn,type_elt | type_elt | 33 | const | 215 | Using index condition; Using temporary; Using filesort |
|
| 1 | SIMPLE | toku_problem_tempologie | ALL | PRIMARY | NULL | NULL | NULL | 4417 | Using where; Using join buffer (flat, BNL join) | |
| 1 | SIMPLE | toku_problem_myisam | ref | PRIMARY,ni,query,agg_sql_ne | query | 14 | toku_problem1.toku_problem_topo.code_elt_sql,toku_problem1.toku_problem_tempologie.d,toku_problem1.toku_problem_tempologie.h | 1 | Using where |
|
+------+-------------+-------------------------+------+-----------------------------+----------+---------+------------------------------------------------------------------------------------------------------------------------------+------+--------------------------------------------------------+
|
But when we execute the same query, using the TokuDB table (toku_problem_tokudb), it uses the PRIMARY index instead of the expected "query" index.
EXPLAIN
|
SELECT * |
FROM
|
(
|
SELECT dt psdate,d,h,so,mo,anneeo,code_elt_sql,type_elt,dn |
FROM |
toku_problem_tempologie
|
INNER JOIN toku_problem_topo |
WHERE LENGTH(dn) IN (39) AND dn REGEXP '0118151|0118152|0118153|0118154|0118155|0118156' AND dn LIKE '0000001,0106486%' AND type_elt ='cellule' AND (dt BETWEEN '2015-06-02 00:00:00' AND '2015-06-16 23:59:59') |
) AS sys |
LEFT JOIN toku_problem_toku ON ((sys.d = toku_problem_toku.`date_debut_mesure` AND sys.h = toku_problem_toku.`heure_debut_mesure`) AND (sys.code_elt_sql = toku_problem_toku.ni)) |
GROUP BY |
LEFT(sys.dn,39),3; |
+------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+
|
| 1 | SIMPLE | toku_problem_topo | ref | dn,type_elt | type_elt | 33 | const | 215 | Using index condition; Using temporary; Using filesort |
|
| 1 | SIMPLE | toku_problem_tempologie | ALL | PRIMARY | NULL | NULL | NULL | 4417 | Using where; Using join buffer (flat, BNL join) | |
| 1 | SIMPLE | toku_problem_toku | ref | PRIMARY,ni,query,agg_sql_ne | PRIMARY | 3 | toku_problem1.toku_problem_tempologie.d | 100 | Using where |
|
+------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+
|
We faced this problem with different versions of MariaDB and TokuDB.
We also faced it several times with different requests, this dataset is just a simplified example.
Please let us know if you need more information.
Thank you