|
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)
|
|
field_temporal_packed.diff