Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.4
-
None
-
Linux
Description
I isolated an amazing, catastrophic lack of performance for a common business operation. Something that with the same tables and data takes 0.5 seconds in MS SQL, in Mariadb it takes 15 minutes, and is not related to the engine, for I tried with innodb, memory and rocksdb with identical results.
two tables
CREATE TABLE test1 (
|
callid varchar(64) NOT NULL DEFAULT '',
|
created datetime NOT NULL DEFAULT current_timestamp,
|
PRIMARY KEY (callid)
|
)
|
ENGINE = INNODB,
|
AVG_ROW_LENGTH = 88,
|
CHARACTER SET latin1,
|
COLLATE latin1_swedish_ci;
|
|
|
CREATE TABLE test2 (
|
callid varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
|
date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
PRIMARY KEY (callid)
|
)
|
ENGINE = INNODB,
|
AVG_ROW_LENGTH = 88,
|
CHARACTER SET latin1,
|
COLLATE latin1_swedish_ci;
|
|
|
fill them with 65.000 rows (in my case)
|
|
|
select c.* from test1 c right join test2 t on c.callid=t.callid where c.callid is null;
|
+--------+---------+
|
| callid | created |
|
+--------+---------+
|
| NULL | NULL |
|
+--------+---------+
|
1 row in set (14 min 35.123 sec)
|
if you try to do
select * from test2 where callid not in (select callid from test1);
the result is the same
This operation takes 1/2 in Ms SQL.
What is wrong?