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

Query very slow compared to Mysql

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

          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-8306 Complete cost-based optimization for ORDER BY with LIMIT

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17111 Estimates are low for a condition that satisfies all the rows of an INNODB table

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            raziel057 Thomas Lallement created issue -
            raziel057 Thomas Lallement made changes -
            Field Original Value New Value
            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.

            SQL 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.
            		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.

            {code:sql}
            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;
            {code}

            ---------------------

            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.
            raziel057 Thomas Lallement made changes -
            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.

            {code:sql}
            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;
            {code}

            ---------------------

            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.
            		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.

            {code:sql}
            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;
            {code}

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

            desc TICKETS;

            {code:sql}
            +-----------------+-------------+------+-----+---------+----------------+
            | 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 | |
            +-----------------+-------------+------+-----+---------+----------------+
            {code}

            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.
            raziel057 Thomas Lallement made changes -
            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.

            {code:sql}
            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;
            {code}

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

            desc TICKETS;

            {code:sql}
            +-----------------+-------------+------+-----+---------+----------------+
            | 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 | |
            +-----------------+-------------+------+-----+---------+----------------+
            {code}

            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.
            		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.

            {code:sql}
            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;
            {code}

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

            desc TICKETS;

            {code:sql}
            +-----------------+-------------+------+-----+---------+----------------+
            | 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 | |
            +-----------------+-------------+------+-----+---------+----------------+
            {code}

            EXPLAIN Query Mysql
            {code:sql}
            +----+-------------+-------+------------+--------+------------------------------+---------+---------+-------------------------------+------+----------+-------------+
            | 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 |
            +----+-------------+-------+------------+--------+------------------------------+---------+---------+-------------------------------+------+----------+-------------+
            {code}

            EXPLAIN Query MariaDb
            {code:sql}
            +----+-------------+-------+--------+--------------------------------+----------------------+---------+-------------------------------+-------+----------------------------------------------+
            | 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 |
            +----+-------------+-------+--------+--------------------------------+---------------
            {code}

            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.
            {code:sql}
            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;
            {code}

            MariaDb | 10.1.28 => 3s

            If I keep all properties in select but remove the "order by" clause it takes less than 50ms.
            elenst Elena Stepanova made changes -
            Component/s Optimizer [ 10200 ]
            Assignee Alice Sherepa [ alice ]
            raziel057 Thomas Lallement made changes -
            Attachment index_tickets.png [ 44837 ]
            raziel057 Thomas Lallement made changes -
            Attachment index_transactions.png [ 44838 ]
            raziel057 Thomas Lallement made changes -
            Attachment index_zones.png [ 44839 ]
            raziel057 Thomas Lallement made changes -
            Attachment my.cnf [ 44840 ]
            raziel057 Thomas Lallement made changes -
            Attachment local_mariadb.conf [ 44841 ]
            raziel057 Thomas Lallement made changes -
            Attachment my.cnf [ 44840 ]
            raziel057 Thomas Lallement made changes -
            Attachment local_mariadb.conf [ 44841 ]
            raziel057 Thomas Lallement made changes -
            Attachment my.conf [ 44842 ]
            psergei Sergei Petrunia made changes -
            Labels performance order-by-optimization performance
            psergei Sergei Petrunia made changes -
            alice Alice Sherepa made changes -
            Assignee Alice Sherepa [ alice ] Varun Gupta [ varun ]
            alice Alice Sherepa made changes -
            Fix Version/s 10.1 [ 16100 ]
            varun Varun Gupta (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            psergei Sergei Petrunia made changes -
            varun Varun Gupta (Inactive) made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            varun Varun Gupta (Inactive) made changes -
            Fix Version/s N/A [ 14700 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Duplicate [ 3 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 84374 ] MariaDB v4 [ 153331 ]

            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.