recenty we've have moved all of our server to new hardware and to MariaDB . I've installed a new server with VirtualMin for managing all of our company's applications. at a first glance most of the application run/feel faster with MariaDB (version 10.6.12). but some paticular query's run for minutes i can't figure out why.... on MySQL the same query's weren't a problem.
we run a HP Proliant DL380 G10 server with Xeon Gold 5118 Dual CPU 24 core with 128 GIG ram. with ESXi 8.0.1 with an VM running Ubuntu 22.04
here is an query that MariaDB is struggling with and i don't know why
SELECT `courses`.*, `course_xml_feed`.`xml_feed_id` AS `pivot_xml_feed_id`,
`course_xml_feed`.`course_id` AS `pivot_course_id`
FROM `courses`
INNER JOIN `course_xml_feed` ON `courses`.`id` = `course_xml_feed`.`course_id`
WHERE `course_xml_feed`.`xml_feed_id` in (12) AND `internet` = 1 AND EXISTS (
SELECT *
FROM `publications`
WHERE `courses`.`id` = `publications`.`course_id` AND `internet` = 1 AND EXISTS (
SELECT *
FROM `locations`
WHERE `publications`.`location_id` = `locations`.`id` AND `internet` = 1 AND `locations`.`deleted_at`
is null) AND EXISTS (
SELECT *
FROM `publication_dates`
INNER JOIN (
SELECT MIN(`publication_dates`.`id`) AS `id_aggregate`, min(`publication_dates`.`date`) AS
`date_aggregate`, `publication_dates`.`publication_id`
FROM `publication_dates`
INNER JOIN (
SELECT MIN(`publication_dates`.`date`) AS `date_aggregate`, `publication_dates`.`publication_id`
FROM `publication_dates`
GROUP BY `publication_dates`.`publication_id`) AS `first_date` ON `first_date`.`date_aggregate` =
`publication_dates`.`date` AND `first_date`.`publication_id` = `publication_dates`.`publication_id`
GROUP BY `publication_dates`.`publication_id`) AS `first_date` ON `first_date`.`id_aggregate` =
`publication_dates`.`id` AND `first_date`.`date_aggregate` = `publication_dates`.`date` AND
`first_date`.`publication_id` = `publication_dates`.`publication_id`
WHERE `publications`.`id` = `publication_dates`.`publication_id` AND `date` >= '2023-11-10' AND `date` < '2023-12-30')) AND
`courses`.`deleted_at` is null
ORDER BY `courses`.`name` asc
time of this query : 00:01:22.2 (+ 0,016 sec. network) */
as of MySQL i took less than a second
Config off MariaDB
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
[mysql]
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[client]
# Example of client certificate usage
#ssl-cert = /etc/mysql/client-cert.pem
#ssl-key = /etc/mysql/client-key.pem
#
# Allow only TLS encrypted connections
#ssl-verify-server-cert = on
# This group is *never* read by mysql client library, though this
# /etc/mysql/mariadb.cnf.d/client.cnf file is not read by Oracle MySQL
# client anyway.
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]
#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#
[mysql]
[mysql_upgrade]
[mysqladmin]
[mysqlbinlog]
[mysqlcheck]
[mysqldump]
[mysqlimport]
[mysqlshow]
[mysqlslap]
[mysqld_safe]
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# especially if they contain "#" chars...
nice = 0
skip_log_error
syslog
[server]
[mysqld]
pid-file = /run/mysqld/mysqld.pid
basedir = /usr
[galera]
# Mandatory settings
#wsrep_on = ON
#wsrep_cluster_name = "MariaDB Galera Cluster"
#wsrep_cluster_address = gcomm://
#binlog_format = row
#default_storage_engine = InnoDB
#innodb_autoinc_lock_mode = 2
# Allow server to accept connections on all interfaces.
#bind-address = 0.0.0.0
# Optional settings
#wsrep_slave_threads = 1
#innodb_flush_log_at_trx_commit = 0
this what ANALYZE returns
+------+--------------------+-------------------+--------+-----------------------------------------------------------------------------------------------+----------------------------------------------+---------+---------------------------------------------------------------------------+-------+----------+----------+------------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+--------------------+-------------------+--------+-----------------------------------------------------------------------------------------------+----------------------------------------------+---------+---------------------------------------------------------------------------+-------+----------+----------+------------+----------------------------------------------+
| 1 | PRIMARY | course_xml_feed | ref | course_xml_feed_course_id_foreign,course_xml_feed_xml_feed_id_foreign | course_xml_feed_xml_feed_id_foreign | 8 | const | 70 | 70.00 | 100.00 | 100.00 | Using temporary; Using filesort |
| 1 | PRIMARY | courses | eq_ref | PRIMARY | PRIMARY | 8 | .course_xml_feed.course_id | 1 | 1.00 | 100.00 | 100.00 | Using where |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 8 | func | 1 | 0.80 | 100.00 | 100.00 | |
| 2 | MATERIALIZED | publications | ALL | publications_course_id_foreign | NULL | NULL | NULL | 9801 | 9727.00 | 100.00 | 47.25 | Using where |
| 4 | DEPENDENT SUBQUERY | publication_dates | ref | PRIMARY,publication_dates_date_publication_id_unique,publication_dates_publication_id_foreign | publication_dates_publication_id_foreign | 8 | .publications.id | 1 | 1.98 | 50.00 | 28.31 | Using where |
| 4 | DEPENDENT SUBQUERY | <derived5> | ref | key0 | key0 | 8 | .publications.id | 2 | 0.90 | 100.00 | 93.49 | Using where |
| 5 | LATERAL DERIVED | publication_dates | ref | publication_dates_date_publication_id_unique,publication_dates_publication_id_foreign | publication_dates_publication_id_foreign | 8 | .publications.id | 1 | 2.97 | 100.00 | 100.00 | Using index condition |
| 5 | LATERAL DERIVED | <derived6> | ref | key0 | key0 | 12 | .publication_dates.date, .publication_dates.publication_id | 2 | 0.34 | 100.00 | 100.00 | |
| 6 | DERIVED | publication_dates | index | publication_dates_publication_id_foreign | publication_dates_date_publication_id_unique | 11 | NULL | 27994 | 27279.00 | 100.00 | 100.00 | Using index; Using temporary; Using filesort |
| 3 | DEPENDENT SUBQUERY | locations | eq_ref | PRIMARY | PRIMARY | 8 | .publications.location_id | 1 | 1.00 | 100.00 | 100.00 | Using where |
+------+--------------------+-------------------+--------+-----------------------------------------------------------------------------------------------+----------------------------------------------+---------+---------------------------------------------------------------------------+-------+----------+----------+------------+----------------------------------------------+
10 rows in set (4 min 57.347 sec)
same query on MySQL
+----+-------------+-------------------+------------+--------+-----------------------------------------------------------------------------------------------+----------------------------------------------+---------+----------------------------------------------------------------------------------------+-------+----------+---------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+--------+-----------------------------------------------------------------------------------------------+----------------------------------------------+---------+----------------------------------------------------------------------------------------+-------+----------+---------------------------------------------------------------+
| 1 | PRIMARY | locations | NULL | ALL | PRIMARY | NULL | NULL | NULL | 50 | 2.00 | Using where; Using temporary; Using filesort; Start temporary |
| 1 | PRIMARY | courses | NULL | ALL | PRIMARY | NULL | NULL | NULL | 200 | 1.00 | Using where; Using join buffer (hash join) |
| 1 | PRIMARY | course_xml_feed | NULL | ref | course_xml_feed_course_id_foreign,course_xml_feed_xml_feed_id_foreign | course_xml_feed_course_id_foreign | 8 | .courses.id | 3 | 12.89 | Using where |
| 1 | PRIMARY | publications | NULL | ref | PRIMARY,publications_course_id_foreign,publications_location_id_foreign | publications_course_id_foreign | 8 | .courses.id | 67 | 0.71 | Using where |
| 1 | PRIMARY | publication_dates | NULL | ref | PRIMARY,publication_dates_date_publication_id_unique,publication_dates_publication_id_foreign | publication_dates_publication_id_foreign | 8 | .publications.id | 2 | 9.43 | Using where |
| 1 | PRIMARY | <derived5> | NULL | ref | <auto_key0> | <auto_key0> | 21 | .publication_dates.id, .publication_dates.date, .publications.id | 10 | 100.00 | Using index; End temporary |
| 5 | DERIVED | <derived6> | NULL | ALL | NULL | NULL | NULL | NULL | 26251 | 100.00 | Using where; Using temporary |
| 5 | DERIVED | publication_dates | NULL | eq_ref | publication_dates_date_publication_id_unique,publication_dates_publication_id_foreign | publication_dates_date_publication_id_unique | 11 | first_date.date_aggregate,first_date.publication_id | 1 | 100.00 | Using index |
| 6 | DERIVED | publication_dates | NULL | index | publication_dates_date_publication_id_unique,publication_dates_publication_id_foreign | publication_dates_publication_id_foreign | 8 | NULL | 26251 | 100.00 | NULL |
+----+-------------+-------------------+------------+--------+-----------------------------------------------------------------------------------------------+----------------------------------------------+---------+----------------------------------------------------------------------------------------+-------+----------+---------------------------------------------------------------+
9 rows in set, 4 warnings (0.00 sec)
Mysql config
mysql.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
/etc/mysql/conf.d
mysqldump.cnf
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
/etc/mysql/mysql.conf.d/mysql.cnf
[mysqld]
user = mysql
mysqlx-bind-address = 127.0.0.1
key_buffer_size = 16M
myisam-recover-options = BACKUP
EXPLAIN ANALYZEfor the query show?SELECT min(id), min(date) ...will not necessarily give thedatethat matched thatid. Cfonly_full_group_byand [groupwise-max]..internetanddate.SHOW CREATE TABLEfor each table so we can know the datatypes and existing indexes.