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

Comparison with temporal columns is slow in MariaDB

    XMLWordPrintable

Details

    Description

      Comparison of a temporal column seems to be slower in MariaDB than it MySQL-8.0. Also, MariaDB-10.4 seems to be slower than MariaDB-10.3.
      These scenarios were tested:

      • field vs string literal
      • field vs temporal literal
      • field vs field

      Analizing the code revealed a problem - MariaDB does not have methods:

      • Field::val_time_packed()
      • Field::val_datetime_packed()
        so comparison of a field involves a call for the generic methods Item::val_time_packed() and Item::val_datetime_packed() which call Item_field::get_date() and Field_xxx::get_date().

      In MySQL, there are virtual methods to get a temporal value in a packed longlong:

      • Field::val_time_temporal()
      • Field::val_date_temporal()

      so the comparison routines in Arg_comparator get a faster access to the packed temporal format.

      A patch adding similar methods to MariaDB demonstrates performance improvement in all scenarios. The patch is attached to this issue.

      Benchmark results follow. The numbers mean query time in seconds, the smaller - the better.

      Note, the comparison of a field to a temporal literal is still slower than in MySQL. This issue will be fixed separately, in Item_date_literal, Item_time_literal and Item_datetime_literal.

      Preparation

      # Create table t3 with 10 rows
      # Create table t2 with 1000 rows
      DROP TABLE IF EXISTS t3,t2;
      CREATE TABLE t3 (a INT);
      INSERT INTO t3 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      CREATE TABLE t2 AS SELECT 1 FROM t3 t3a, t3 t3b, t3 t3c;
      

      datetime_field=string_literal

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DATETIME) ENGINE=HEAP;
      INSERT INTO t1 SELECT '2001-01-01 10:20:30' FROM t2,t2 t2a;
      SELECT COUNT(*) FROM t1 WHERE a='2001-01-01 10:20:30';
      

      My80:  0.035
      10.3:  0.049
      10.4:  0.049 (orig)
      10.4:  0.032 (fixed)
      

      datetime_field=datetime_literal

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DATETIME) ENGINE=HEAP;
      INSERT INTO t1 SELECT '2001-01-01 10:20:30' FROM t2,t2 t2a;
      SELECT COUNT(*) FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:30';
      

      My80:  0.035
      10.3:  0.053
      10.4:  0.058 (orig)
      10.4:  0.045 (fixed)
      

      datetime_field=datetime_field

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1
      (
        a DATETIME,
        b DATETIME
      ) ENGINE=HEAP;
      INSERT INTO t1 SELECT '2001-01-01 10:20:30','2001-01-01 10:20:30' FROM t2,t2 t2a;
      SELECT COUNT(*) FROM t1 WHERE a=b;
      

      My80: 0.035
      10.3: 0.058
      10.4: 0.058 (orig)
      10.4: 0.039 (fixed)
      

      time_field=string_literal

      DROP TABLE IF EXISTS t1;
      INSERT INTO t1 SELECT '10:20:30' FROM t2,t2 t2a;
      CREATE TABLE t1 (a TIME) ENGINE=HEAP;
      SELECT COUNT(*) FROM t1 WHERE a='10:20:30';
      

      My80: 0.034
      10.3: 0.038
      10.4: 0.046 (orig)
      10.4: 0.037 (fixed)
      

      time_field=time_literal

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a TIME) ENGINE=HEAP;
      INSERT INTO t1 SELECT '10:20:30' FROM t2,t2 t2a;
      SELECT COUNT(*) FROM t1 WHERE a=TIME'10:20:30';
      

      My80: 0.035
      10.3: 0.038
      10.4: 0.049 (orig)
      10.4: 0.039 (fixed)
      

      time_field=time_field_literal

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1
      (
        a TIME,
        b TIME
      ) ENGINE=HEAP;
      INSERT INTO t1 SELECT '10:20:30','10:20:30' FROM t2,t2 t2a;
      SELECT COUNT(*) FROM t1 WHERE a=b;
      

      My80: 0.034
      10.3: 0.042
      10.4: 0.051 (orig)
      10.4: 0.036 (fixed)
      

      date_field=string_literal

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DATE) ENGINE=HEAP;
      INSERT INTO t1 SELECT '2001-01-01' FROM t2,t2 t2a;
      SELECT COUNT(*) FROM t1 WHERE a='2001-01-01';
      

      My80: 0.038 -- TODO
      10.3: 0.045
      10.4: 0.048 (orig)
      10.4: 0.039 (fixed)
      

      date_field=date_literal

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DATE) ENGINE=HEAP;
      INSERT INTO t1 SELECT '2001-01-01' FROM t2,t2 t2a;
      SELECT COUNT(*) FROM t1 WHERE a=DATE'2001-01-01';
      

      My80: 0.036 -- TODO
      10.3: 0.051
      10.4: 0.053 (orig)
      10.4: 0.045 (fixed)
      

      date_field=date_literal

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1
      (
        a DATE,
        b DATE
      ) ENGINE=HEAP;
      INSERT INTO t1 SELECT '2001-01-01','2001-01-01' FROM t2,t2 t2a;
      SELECT COUNT(*) FROM t1 WHERE a=b;
      

      My80: 0.038
      10.3: 0.049
      10.4: 0.049 (orig)
      10.4: 0.038 (fixed)
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.