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

MariaDB doesn't use the expected indexes with TokuDB

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5.38, 10.0, 10.1
    • Fix Version/s: 10.1
    • Labels:
    • Environment:
      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

        Attachments

        1. my.cnf
          5 kB
        2. real.my.cnf
          5 kB
        3. toku_problem1_database.sql
          2.79 MB

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              osiris-support Osiris Support
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated: