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

Query very slow compared to Mysql

    XMLWordPrintable

Details

    Description

      I identified an issue which happens on MariaDb because a query is very very slow compared to the same query executed on the same database schema / data /engine (innodb) in Mysql. I give you the figures to show the difference for this query.

      SELECT SQL_NO_CACHE t0_.ID_TICKETS AS ID_TICKETS_0, t0_.MSISDN AS MSISDN_1, t0_.IMMAT AS IMMAT_2, t0_.OPERATEUR AS OPERATEUR_3, t0_.DHDEBUT AS DHDEBUT_4, t0_.DHMAX AS DHMAX_5, t0_.DHFIN AS DHFIN_6, t0_.PRIX AS PRIX_7, t0_.TYPE_APPEL AS TYPE_APPEL_8, t0_.PAYS AS PAYS_9, t1_.ID AS ID_10, t1_.DHTRANS AS DHTRANS_11, t1_.MONTANT AS MONTANT_12, t0_.ID_CITOYENS AS ID_CITOYENS_13, t0_.ID_ZONES AS ID_ZONES_14, t0_.STATUT AS STATUT_15, t0_.ID_TRANSACTIONS AS ID_TRANSACTIONS_16, t1_.ID_CITOYENS AS ID_CITOYENS_17, t1_.STATUT AS STATUT_18, t1_.locality_id AS locality_id_19 
      FROM TICKETS t0_ LEFT JOIN TRANSACTIONS t1_ ON t0_.ID_TRANSACTIONS = t1_.ID 
      INNER JOIN ZONES z2_ ON t0_.ID_ZONES = z2_.ID AND (z2_.ID_LOCALITE = 1) 
      ORDER BY t0_.DHDEBUT ASC LIMIT 10 OFFSET 0;
      

      MariaDb | 10.1.28 => 12s
      Mysql | 5.7.20 => 0.00s

      desc TICKETS;

      +-----------------+-------------+------+-----+---------+----------------+
      | Field           | Type        | Null | Key | Default | Extra          |
      +-----------------+-------------+------+-----+---------+----------------+
      | ID_TICKETS      | int(11)     | NO   | PRI | NULL    | auto_increment |
      | ID_CITOYENS     | int(11)     | NO   | MUL | NULL    |                |
      | ID_ZONES        | int(11)     | NO   | MUL | NULL    |                |
      | MSISDN          | varchar(20) | YES  | MUL | NULL    |                |
      | IMMAT           | varchar(20) | YES  | MUL | NULL    |                |
      | OPERATEUR       | int(11)     | NO   |     | NULL    |                |
      | DHDEBUT         | datetime    | NO   | MUL | NULL    |                |
      | DHMAX           | datetime    | NO   |     | NULL    |                |
      | DHFIN           | datetime    | YES  |     | NULL    |                |
      | PRIX            | int(11)     | NO   |     | NULL    |                |
      | STATUT          | int(11)     | NO   | MUL | NULL    |                |
      | TYPE_APPEL      | varchar(10) | NO   |     | NULL    |                |
      | PAYS            | varchar(3)  | NO   |     | NULL    |                |
      | ID_TRANSACTIONS | int(11)     | YES  | MUL | NULL    |                |
      +-----------------+-------------+------+-----+---------+----------------+
      

      EXPLAIN Query Mysql

      +----+-------------+-------+------------+--------+------------------------------+---------+---------+-------------------------------+------+----------+-------------+
      | id | select_type | table | partitions | type   | possible_keys                | key     | key_len | ref                           | rows | filtered | Extra       |
      +----+-------------+-------+------------+--------+------------------------------+---------+---------+-------------------------------+------+----------+-------------+
      |  1 | SIMPLE      | t0_   | NULL       | index  | IDX_6B0363EEAAD06922         | DHDEBUT | 5       | NULL                          |   10 |   100.00 | NULL        |
      |  1 | SIMPLE      | z2_   | NULL       | eq_ref | PRIMARY,IDX_729E73D8AAFE1877 | PRIMARY | 4       | CALL2PARK.t0_.ID_ZONES        |    1 |   100.00 | Using where |
      |  1 | SIMPLE      | t1_   | NULL       | eq_ref | PRIMARY                      | PRIMARY | 4       | CALL2PARK.t0_.ID_TRANSACTIONS |    1 |   100.00 | NULL        |
      +----+-------------+-------+------------+--------+------------------------------+---------+---------+-------------------------------+------+----------+-------------+
      

      EXPLAIN Query MariaDb

      +----+-------------+-------+--------+--------------------------------+----------------------+---------+-------------------------------+-------+----------------------------------------------+
      | id | select_type | table | type   | possible_keys                  | key                  | key_len | ref                           | rows  | Extra                                        |
      +----+-------------+-------+--------+--------------------------------+----------------------+---------+-------------------------------+-------+----------------------------------------------+
      |  1 | SIMPLE      | z2_   | ref    | PRIMARY,IDX_729E73D8AAFE1877   | IDX_729E73D8AAFE1877 | 4       | const                         |    14 | Using index; Using temporary; Using filesort |
      |  1 | SIMPLE      | t0_   | ref    | IDX_6B0363EEAAD06922           | IDX_6B0363EEAAD06922 | 4       | call2park.z2_.ID              | 49471 |                                              |
      |  1 | SIMPLE      | t1_   | eq_ref | PRIMARY                        | PRIMARY              | 4       | call2park.t0_.ID_TRANSACTIONS |     1 | Using where                                  |
      +----+-------------+-------+--------+--------------------------------+---------------
      

      If I remove some properties from the select in query it's a lot faster. It's very strange because I get only 10 rows.

      SELECT SQL_NO_CACHE t0_.ID_TICKETS AS ID_TICKETS_0, t1_.ID AS ID_10
      FROM TICKETS t0_ LEFT JOIN TRANSACTIONS t1_ ON t0_.ID_TRANSACTIONS = t1_.ID 
      INNER JOIN ZONES z2_ ON t0_.ID_ZONES = z2_.ID AND (z2_.ID_LOCALITE = 1) ORDER BY t0_.DHDEBUT ASC LIMIT 10 OFFSET 0;
      

      MariaDb | 10.1.28 => 3s

      If I keep all properties in select but remove the "order by" clause it takes less than 50ms.

      Attachments

        1. index_tickets.png
          index_tickets.png
          51 kB
        2. index_transactions.png
          index_transactions.png
          29 kB
        3. index_zones.png
          index_zones.png
          17 kB
        4. my.conf
          4 kB

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              raziel057 Thomas Lallement
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.