Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.11
-
None
-
None
-
Debian 12
Description
MariaDB [pmacontrol]> select version();
|
+----------------------------+
|
| version() |
|
+----------------------------+
|
| 10.11.11-MariaDB-deb12-log | |
+----------------------------+
|
1 row in set (0,001 sec) |
Here i am interested on query "fast", but not fast enough it's should be :
MariaDB [pmacontrol]> SELECT `a`.`id_mysql_server`, `a`.`id_ts_file`, MIN(`a`.`date`) AS `min_date`, MAX(`a`.`date`) AS `max_date`, COUNT(1) AS `cpt`, `file_name` AS `ts_file` FROM `ts_date_by_server` `a` INNER JOIN ( SELECT `id_mysql_server`, `id_ts_file`, `last_date_listener` FROM `ts_max_date` `x` WHERE `x`.`id_ts_file` = 218 AND `last_date_listener` != `date` AND `x`.`id_mysql_server` = 1 ) `b` ON `a`.`id_mysql_server` = `b`.`id_mysql_server` AND `a`.`id_ts_file` = `b`.`id_ts_file` INNER JOIN `ts_file` `c` ON `c`.`id` = `a`.`id_ts_file` WHERE `a`.`date` > `b`.`last_date_listener` AND a.date < now() AND `a`.`id_mysql_server` = 1 GROUP BY `id_mysql_server`, `id_ts_file`; |
+-----------------+------------+---------------------+---------------------+------+--------------+
|
| id_mysql_server | id_ts_file | min_date | max_date | cpt | ts_file |
|
+-----------------+------------+---------------------+---------------------+------+--------------+
|
| 1 | 218 | 2025-03-09 21:24:54 | 2025-03-10 00:29:33 | 1102 | mysql_global | |
+-----------------+------------+---------------------+---------------------+------+--------------+
|
1 row in set (0,045 sec) |
First I didn't paid attention, after i try to understand where go the time 0.05, because if i cut this query in 2 each part less 0.001.
MariaDB [pmacontrol]> SELECT
|
-> `id_mysql_server`,
|
-> `id_ts_file`,
|
-> `last_date_listener`
|
-> FROM
|
-> `ts_max_date` `x`
|
-> WHERE
|
-> `x`.`id_ts_file` = 218 |
-> AND `last_date_listener` != `date`
|
-> AND `x`.`id_mysql_server` = 1; |
+-----------------+------------+---------------------+
|
| id_mysql_server | id_ts_file | last_date_listener |
|
+-----------------+------------+---------------------+
|
| 1 | 218 | 2025-03-09 21:24:44 | |
+-----------------+------------+---------------------+
|
1 row in set (0,000 sec) |
and :
SELECT
|
`a`.`id_mysql_server`,
|
`a`.`id_ts_file`,
|
MIN(`a`.`date`) AS `min_date`,
|
MAX(`a`.`date`) AS `max_date`,
|
COUNT(1) AS `cpt` |
FROM
|
`ts_date_by_server` `a`
|
WHERE
|
`a`.`id_mysql_server` = 1 AND `a`.`id_ts_file` = 218 AND |
`a`.`date` > "2025-03-09 21:24:44" |
AND `a`.`date` <= NOW()
|
GROUP BY
|
`a`.`id_mysql_server`,
|
`a`.`id_ts_file`;
|
+-----------------+------------+---------------------+---------------------+------+
|
| id_mysql_server | id_ts_file | min_date | max_date | cpt |
|
+-----------------+------------+---------------------+---------------------+------+
|
| 1 | 218 | 2025-03-09 21:24:54 | 2025-03-10 00:29:33 | 1102 | |
+-----------------+------------+---------------------+---------------------+------+
|
1 row in set (0,001 sec) |
|
I thought maybe a problem because i mixed storage engine (one with RocksDB and other one with InnoDB), then i migrate both on RocksDB same result.
Then i converted biggest one to innoDB and the result there :
MariaDB [pmacontrol]> WITH b AS (
|
-> SELECT
|
-> `id_mysql_server`,
|
-> `id_ts_file`,
|
-> `last_date_listener`
|
-> FROM
|
-> `ts_max_date` `x`
|
-> WHERE
|
-> `x`.`id_ts_file` = 218 |
-> AND `last_date_listener` != `date`
|
-> AND `x`.`id_mysql_server` = 1 |
-> LIMIT 1 |
-> )
|
-> SELECT
|
-> a.id_mysql_server,
|
-> a.id_ts_file,
|
-> MIN(a.date) AS min_date,
|
-> MAX(a.date) AS max_date,
|
-> COUNT(*) AS cpt
|
-> FROM gg4 a
|
-> INNER JOIN b
|
-> ON a.id_mysql_server = b.id_mysql_server
|
-> AND a.id_ts_file = b.id_ts_file
|
-> WHERE a.date > b.last_date_listener;
|
+-----------------+------------+---------------------+---------------------+-----+
|
| id_mysql_server | id_ts_file | min_date | max_date | cpt |
|
+-----------------+------------+---------------------+---------------------+-----+
|
| 1 | 218 | 2025-03-09 21:24:54 | 2025-03-09 21:59:57 | 210 | |
+-----------------+------------+---------------------+---------------------+-----+
|
1 row in set (0,002 sec) |
Something let me guess there is something wrong with the optimizer with RocksDB. I have no idea where gone these 0.035 sec