[MDEV-15339] Same Query extremely slower on MariaDB 10.3.4 than on MariaDB 10.3.2 Created: 2018-02-16  Updated: 2020-01-06  Resolved: 2018-05-28

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.3.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Regine Rosewich Assignee: Alice Sherepa
Resolution: Incomplete Votes: 0
Labels: need_feedback, optimizer, query, subquery
Environment:

RHEL 7.4 / OSX 10.13.3

Attachments: Text File MariaDB_query_problem.txt    

 Description   

I have two identical databases running on two different machines:
1. iMac (Late 2012, QuadCore i7, 32GB, OSX 10.13.3) - Development
2. Intel-Server(Late 2017, 20-Core XEON, 64GB, RHEL 7.4) - Production

The iMac is a multi-purpose machine with MariaDB-Server (homebrew) 10.3.2
The second machine is a dedicated DB-Server running only MariaDB 10.3.4 as DBMS

The following query takes 200ms for 200 rows on the iMac whereas it takes 6 to 8 seconds! on the RHEL Database Machine (which is 30 to 40 times slower on the far better machine).

SELECT * FROM dev_welu_pdm.appadaptive_pdm_domain_model_stammdaten_artikel a LEFT JOIN (SELECT b.artikel, GROUP_CONCAT(c.reference) AS referenz FROM dev_welu_pdm.appadaptive_pdm_domain_model_stammdaten_artikelreference b
 
INNER JOIN dev_welu_pdm.appadaptive_pdm_domain_model_klassifizierung_reference c ON b.reference = c.persistence_object_identifier GROUP BY b.artikel) AS x on a.persistence_object_identifier = x.artikel

Running the query with EXPLAIN the result is different on the two machines. (See the attached file)

CREATE TABLE `appadaptive_pdm_domain_model_stammdaten_artikel` (
 
  `persistence_object_identifier` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
 
  `artikelnr` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
 
  `bezeichnung` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `produkt` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `bonus` tinyint(1) NOT NULL,
 
  `warengruppe` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `supplychain` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `status` int(11) NOT NULL,
 
  `mindestbestellmenge` int(11) NOT NULL,
 
  `verpackungmenge` int(11) NOT NULL,
 
  `ekmindestbestellmenge` int(11) NOT NULL,
 
  `laenge` int(11) NOT NULL,
 
  `breite` int(11) NOT NULL,
 
  `hoehe` int(11) NOT NULL,
 
  `bestelllosgroesse` int(11) NOT NULL,
 
  `gewicht` double NOT NULL,
 
  `zolltarifnummer` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `gebuehrendsd` double NOT NULL,
 
  `markenanonym` tinyint(1) NOT NULL,
 
  `altartikelnr` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `mengeneinheit` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `durchmesser` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `standardlieferant` int(11) NOT NULL,
 
  `varkey` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `klassegruppe` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `branding` int(11) NOT NULL,
 
  `verpackung` int(11) NOT NULL,
 
  `zeichnungsnummer` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `eigenschaften` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:array)',
 
  `verwendungen` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:array)',
 
  `mkategorieen` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:array)',
 
  `gdimandant` int(11) NOT NULL,
 
  `statustype` int(11) NOT NULL,
 
  `statushistory` longtext COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:array)',
 
  `imagelink` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `qc` tinyint(1) NOT NULL,
 
  `qp` tinyint(1) NOT NULL,
 
  `artikelart` int(11) NOT NULL,
 
  `gdiwgr` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `gdiselektion` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `isoa2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `gdilazo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `gdikzhsl` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `bulkprocessflag` int(11) NOT NULL,
 
  `verwendung` int(11) NOT NULL,
 
  `verkauflosgroesse` int(11) NOT NULL,
 
  `lieferzeit` int(11) NOT NULL,
 
  `pmchecked` tinyint(1) NOT NULL,
 
  `isstandard` tinyint(1) NOT NULL,
 
  `referenz` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `kzal` tinyint(1) NOT NULL,
 
  `kzsperre` tinyint(1) NOT NULL,
 
  PRIMARY KEY (`persistence_object_identifier`),
 
  UNIQUE KEY `UNIQ_307622BC79EBCBC` (`artikelnr`),
 
  KEY `altArtikelNr` (`altartikelnr`),
 
  CONSTRAINT `FK_9D3C6D1D21E3D446` FOREIGN KEY (`persistence_object_identifier`) REFERENCES `appadaptive_pdm_domain_model_stammdaten_abstractstammdatum` (`persistence_object_identifier`) ON DELETE CASCADE
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


CREATE TABLE `appadaptive_pdm_domain_model_stammdaten_artikelreference` (
 
  `persistence_object_identifier` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
 
  `artikel` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `reference` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  PRIMARY KEY (`persistence_object_identifier`),
 
  KEY `IDX_3062FD0DA4375C33` (`artikel`),
 
  KEY `IDX_3062FD0DAEA34913` (`reference`),
 
  CONSTRAINT `FK_FDC375A6A4375C33` FOREIGN KEY (`artikel`) REFERENCES `appadaptive_pdm_domain_model_stammdaten_artikel` (`persistence_object_identifier`),
 
  CONSTRAINT `FK_FDC375A6AEA34913` FOREIGN KEY (`reference`) REFERENCES `appadaptive_pdm_domain_model_klassifizierung_reference` (`persistence_object_identifier`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


CREATE TABLE `appadaptive_pdm_domain_model_klassifizierung_reference` (
 
  `persistence_object_identifier` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
 
  `reference` int(11) NOT NULL,
 
  `bezeichnung` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  PRIMARY KEY (`persistence_object_identifier`),
 
  KEY `bezeichnung` (`bezeichnung`),
 
  KEY `UNIQ_586EC305AEA34913` (`reference`) USING BTREE,
 
  CONSTRAINT `FK_A17A64547A46B0A` FOREIGN KEY (`persistence_object_identifier`) REFERENCES `appadaptive_pdm_domain_model_stammdaten_abstractstammdatum` (`persistence_object_identifier`) ON DELETE CASCADE
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

iMac - Server.cnf

# Example MariaDB config file for very large systems.
 
#
 
# This is for a large system with memory of 1G-2G where the system runs mainly
 
# MariaDB.
 
#
 
# MariaDB programs look for option files in a set of
 
# locations which depend on the deployment platform.
 
# You can copy this option file to one of those
 
# locations. For information about these locations, do:
 
# 'my_print_defaults --help' and see what is printed under
 
# Default options are read from the following files in the given order:
 
# More information at: http://dev.mysql.com/doc/mysql/en/option-files.html
 
#
 
# In this file, you can use all long options that a program supports.
 
# If you want to know which options a program supports, run the program
 
# with the "--help" option.
 
 
 
# The following options will be passed to all MySQL clients
 
[client]
 
#password	= your_password
 
port		= 3306
 
socket		= /tmp/mysql.sock
 
 
 
# Here follows entries for some specific programs
 
 
 
# The MySQL server
 
[mysqld]
 
port		= 3306
 
socket		= /tmp/mysql.sock
 
#skip-external-locking
 
#thread_stack = 128K
 
#table_cache = 8192
 
#tmp_table_size = 320M
 
max_heap_table_size = 320M
 
#query_cache_type = 1
 
query_cache_limit = 64M
 
#query_cache_min_res_unit = 2k
 
query_cache_size = 64M
 
#key_buffer_size = 384M
 
max_allowed_packet = 1M
 
#table_open_cache = 512
 
#sort_buffer_size = 2M
 
#read_buffer_size = 2M
 
#read_rnd_buffer_size = 8M
 
#myisam_sort_buffer_size = 64M
 
#thread_handling = pool-of-threads
 
#thread_cache_size = 8
 
# Try number of CPU's*2 for thread_concurrency
 
#thread_pool_size = 8
 
#thread_pool_priority = auto
 
 
 
# Point the following paths to a dedicated disk
 
#tmpdir		= /tmp/
 
 
 
# Skip reverse DNS lookup of clients
 
skip-name-resolve
 
 
 
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
 
# if all processes that need to connect to mysqld run on the same host.
 
# All interaction with mysqld must be made via Unix sockets or named pipes.
 
# Note that using this option without enabling named pipes on Windows
 
# (via the "enable-named-pipe" option) will render mysqld useless!
 
# 
#skip-networking
 
 
 
# Replication Master Server (default)
 
# binary logging is required for replication
 
# log-bin=mysql-bin
 
 
 
# required unique id between 1 and 2^32 - 1
 
# defaults to 1 if master-host is not set
 
# but will not function as a master if omitted
 
server-id	= 1
 
 
 
# Replication Slave (comment out master section to use this)
 
#
 
# To configure this host as a replication slave, you can choose between
 
# two methods :
 
#
 
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
 
#    the syntax is:
 
#
 
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
 
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
 
#
 
#    where you replace <host>, <user>, <password> by quoted strings and
 
#    <port> by the master's port number (3306 by default).
 
#
 
#    Example:
 
#
 
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
 
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
 
#
 
# OR
 
#
 
# 2) Set the variables below. However, in case you choose this method, then
 
#    start replication for the first time (even unsuccessfully, for example
 
#    if you mistyped the password in master-password and the slave fails to
 
#    connect), the slave will create a master.info file, and any later
 
#    change in this file to the variables' values below will be ignored and
 
#    overridden by the content of the master.info file, unless you shutdown
 
#    the slave server, delete master.info and restart the slaver server.
 
#    For that reason, you may want to leave the lines below untouched
 
#    (commented) and instead use CHANGE MASTER TO (see above)
 
#
 
# required unique id between 2 and 2^32 - 1
 
# (and different from the master)
 
# defaults to 2 if master-host is set
 
# but will not function as a slave if omitted
 
#server-id       = 2
 
#
 
# The replication master for this slave - required
 
#master-host     =   <hostname>
 
#
 
# The username the slave will use for authentication when connecting
 
# to the master - required
 
#master-user     =   <username>
 
#
 
# The password the slave will authenticate with when connecting to
 
# the master - required
 
#master-password =   <password>
 
#
 
# The port the master is listening on.
 
# optional - defaults to 3306
 
#master-port     =  <port>
 
#
 
# binary logging - not required for slaves, but recommended
 
#log-bin=mysql-bin
 
#
 
# binary logging format - mixed recommended 
#binlog_format=mixed
 
 
 
# Uncomment the following if you are using InnoDB tables
 
#innodb_force_recovery = 0
 
innodb_data_home_dir = /usr/local/var/mysql/
 
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
 
innodb_file_per_table
 
innodb_log_group_home_dir = /usr/local/var/mysql
 
# You can set .._buffer_pool_size up to 50 - 80 %
 
# of RAM but beware of setting memory usage too high
 
innodb_buffer_pool_size = 5G
 
# innodb_additional_mem_pool_size = 20M
 
# Set .._log_file_size to 25 % of buffer pool size
 
innodb_log_file_size = 2500M
 
innodb_log_buffer_size = 8M
 
#innodb_flush_log_at_trx_commit = 1
 
#innodb_lock_wait_timeout = 50
 
#innodb_large_prefix = 1
 
 
 
[mysqldump]
 
quick
 
max_allowed_packet = 16M
 
 
 
[mysql]
 
no-auto-rehash
 
# Remove the next comment character if you are not familiar with SQL
 
#safe-updates
 
 
 
[myisamchk]
 
#key_buffer_size = 256M
 
#sort_buffer_size = 256M
 
#read_buffer = 2M
 
#write_buffer = 2M
 
 
 
[mysqlhotcopy]
 
interactive-timeout

Intel-Server (RHEL) - Server.cnf

#
 
# These groups are read by MariaDB server.
 
# Use it for options that only the server (but not clients) should see
 
#
 
# See the examples of server my.cnf files in /usr/share/mysql/
 
#
 
 
 
[client]
 
#password	= your_password
 
port		= 3306
 
socket		= /databases/mysql/mysql.sock
 
 
 
# this is read by the standalone daemon and embedded servers
 
[server]
 
 
 
# this is only for the mysqld standalone daemon
 
[mysqld]
 
 
 
#
 
# * Galera-related settings
 
#
 
[galera]
 
# Mandatory settings
 
#wsrep_on=ON
 
#wsrep_provider=
 
#wsrep_cluster_address=
 
#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 setting
 
#wsrep_slave_threads=1
 
#innodb_flush_log_at_trx_commit=0
 
 
 
# this is only for embedded server
 
[embedded]
 
 
 
# This group is only read by MariaDB servers, not by MySQL.
 
# If you use the same .cnf file for MySQL and MariaDB,
 
# you can put MariaDB-only options here
 
[mariadb]
 
 
 
port		= 3306
 
socket		= /databases/mysql/mysql.sock
 
datadir 	= /databases/mysql
 
#skip-external-locking
 
#thread_stack = 128K
 
#table_cache = 8192
 
#tmp_table_size = 320M
 
max_heap_table_size = 320M
 
#query_cache_type = 1
 
query_cache_limit = 64M
 
query_cache_min_res_unit = 2k
 
query_cache_size = 64M
 
#key_buffer_size = 384M
 
max_allowed_packet = 1M
 
#table_open_cache = 512
 
#sort_buffer_size = 2M
 
#read_buffer_size = 2M
 
#read_rnd_buffer_size = 8M
 
#myisam_sort_buffer_size = 64M
 
#thread_handling = pool-of-threads
 
#thread_cache_size = 8
 
# Try number of CPU's*2 for thread_concurrency
 
#thread_pool_size = 4
 
#thread_pool_priority = auto
 
 
 
# Point the following paths to a dedicated disk
 
#tmpdir		= /tmp/
 
 
 
# Skip reverse DNS lookup of clients
 
skip-name-resolve
 
 
 
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
 
# if all processes that need to connect to mysqld run on the same host.
 
# All interaction with mysqld must be made via Unix sockets or named pipes.
 
# Note that using this option without enabling named pipes on Windows
 
# (via the "enable-named-pipe" option) will render mysqld useless!
 
# 
#skip-networking
 
 
 
# Replication Master Server (default)
 
# binary logging is required for replication
 
# log-bin=mysql-bin
 
 
 
# required unique id between 1 and 2^32 - 1
 
# defaults to 1 if master-host is not set
 
# but will not function as a master if omitted
 
server-id	= 1
 
 
 
# Replication Slave (comment out master section to use this)
 
#
 
# To configure this host as a replication slave, you can choose between
 
# two methods :
 
#
 
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
 
#    the syntax is:
 
#
 
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
 
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
 
#
 
#    where you replace <host>, <user>, <password> by quoted strings and
 
#    <port> by the master's port number (3306 by default).
 
#
 
#    Example:
 
#
 
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
 
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
 
#
 
# OR
 
#
 
# 2) Set the variables below. However, in case you choose this method, then
 
#    start replication for the first time (even unsuccessfully, for example
 
#    if you mistyped the password in master-password and the slave fails to
 
#    connect), the slave will create a master.info file, and any later
 
#    change in this file to the variables' values below will be ignored and
 
#    overridden by the content of the master.info file, unless you shutdown
 
#    the slave server, delete master.info and restart the slaver server.
 
#    For that reason, you may want to leave the lines below untouched
 
#    (commented) and instead use CHANGE MASTER TO (see above)
 
#
 
# required unique id between 2 and 2^32 - 1
 
# (and different from the master)
 
# defaults to 2 if master-host is set
 
# but will not function as a slave if omitted
 
#server-id       = 2
 
#
 
# The replication master for this slave - required
 
#master-host     =   <hostname>
 
#
 
# The username the slave will use for authentication when connecting
 
# to the master - required
 
#master-user     =   <username>
 
#
 
# The password the slave will authenticate with when connecting to
 
# the master - required
 
#master-password =   <password>
 
#
 
# The port the master is listening on.
 
# optional - defaults to 3306
 
#master-port     =  <port>
 
#
 
# binary logging - not required for slaves, but recommended
 
#log-bin=mysql-bin
 
#
 
# binary logging format - mixed recommended 
#binlog_format=mixed
 
 
 
# Uncomment the following if you are using InnoDB tables
 
#innodb_force_recovery = 0
 
innodb_data_home_dir = /databases/mysql/
 
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
 
innodb_file_per_table
 
innodb_log_group_home_dir = /databases/mysql
 
# You can set .._buffer_pool_size up to 50 - 80 %
 
# of RAM but beware of setting memory usage too high
 
innodb_buffer_pool_size = 32G
 
# innodb_additional_mem_pool_size = 20M
 
# Set .._log_file_size to 25 % of buffer pool size
 
innodb_log_file_size = 8G
 
innodb_log_buffer_size = 8M
 
#innodb_flush_log_at_trx_commit = 1
 
#innodb_lock_wait_timeout = 50
 
#innodb_large_prefix = 1
 
 
 
[mysql]
 
no-auto-rehash
 
 
 
# This group is only read by MariaDB-10.1 servers.
 
# If you use the same .cnf file for MariaDB of different versions,
 
# use this group for options that older servers don't understand
 
[mariadb-10.1]



 Comments   
Comment by Alice Sherepa [ 2018-02-17 ]

Please add SHOW CREATE TABLE for all tables, involved in the query and .cnf files

Comment by Regine Rosewich [ 2018-02-18 ]

I think I found the reason for the difference in behaviour between MariaDB 10.3.2 and 10.3.4:

MariaDB 10.3.2 has an optimizer_switch "split_grouping_derived=on" which is refused on MariaDB 10.3.4 (ERROR 1231 (42000): Variable 'optimizer_switch' can't be set to the value of 'split_grouping_derived=on').

As described in task MDEV-13369, this switch does exactly what is shown in the SELECT EXPLAIN result of my iMac

Comment by Igor Babaev [ 2018-03-05 ]

Hi Regine,

How many groups do you have?
How many matches in your left join do you have?

Comment by Regine Rosewich [ 2018-03-06 ]

Hi Igor,

what do you mean with group and matches? Do you mean groups in the GROUP BY Clause and matches in the LEFT JOIN... WHERE? If so, it's only one group and one match usually. But to cut a long story short, I downgraded the MariaDB Version on the RHEL 7.4 Server from 10.3.4 to 10.3.2 and everything runs fine now. The query in question now takes around 100ms on the RHEL 7.4 Server compared to ~6.5s before.

The reason definitely is the optimizer switch "split_grouping_derived=on" which is no longer available on Version 10.3.4 of MariaDB.

Regards

Regine

Comment by Igor Babaev [ 2018-03-07 ]

Hi Regina,
In 10.3.4 we have "split_materialized" that is always on (we just renamed split_grouping_derived). The main difference is that the optimization is now chosen only if the cost of its usage promises a better cost for the query. That's why I'm asking about the number of groups in the derived table with GROUP BY and about the number of rows in the left operand of the LEFT JOIN that have matches in the right operand.

Comment by Alice Sherepa [ 2018-03-07 ]

Reggaeny,
I tried to reproduce the case, on 10.3.4 with my data LATERAL DEchosens choosen only, when it is faster.

Maybe it will be suitable for you to upload a dump of this 3 tables on ftp.askmonty.org/private, so I will try to find out what is going on there.

MariaDB [test]> select version();
 
+----------------+
 
| version()      |
 
+----------------+
 
| 10.3.4-MariaDB |
 
+----------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> CREATE TABLE `t1` (`id` varchar(40) NOT NULL PRIMARY KEY) ENGINE=InnoDB;
 
Query OK, 0 rows affected (0.074 sec)
 
 
 
MariaDB [test]> INSERT INTO t1 SELECT seq FROM seq_1_to_1000;
 
Query OK, 1000 rows affected (0.024 sec)
 
Records: 1000  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> CREATE TABLE `t2` (
 
    ->   `id` int NOT NULL AUTO_INCREMENT,
 
    ->   `reference` int NOT NULL,
 
    ->   PRIMARY KEY (`id`),
 
    ->   KEY `UNIQ_586EC305AEA34913` (`reference`) USING BTREE
 
    -> ) ENGINE=InnoDB;
 
Query OK, 0 rows affected (0.076 sec)
 
 
 
MariaDB [test]> INSERT INTO t2 (reference) SELECT seq FROM seq_1_to_1000;
 
Query OK, 1000 rows affected (0.021 sec)
 
Records: 1000  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> CREATE TABLE `t3` (
 
    ->   id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
    ->   `artikel` varchar(40) DEFAULT NULL,
 
    ->   `reference` varchar(40) DEFAULT NULL,
 
    ->   KEY `IDX_3062FD0DA4375C33` (`artikel`),
 
    ->   KEY `IDX_3062FD0DAEA34913` (`reference`)
 
    -> ) ENGINE=InnoDB;
 
Query OK, 0 rows affected (0.077 sec)
 
 
 
MariaDB [test]> INSERT INTO t3 (artikel,reference) SELECT seq,seq FROM seq_1_to_1000;
 
Query OK, 1000 rows affected (0.055 sec)
 
Records: 1000  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> ANALYZE
 
    -> SELECT * FROM t1
 
    ->   LEFT JOIN
 
    ->   (SELECT t3.artikel, GROUP_CONCAT(t2.reference)
 
    ->    FROM t3 INNER JOIN t2 ON t3.reference = t2.id
 
    ->    GROUP BY t3.artikel) AS x
 
    ->   ON t1.id = x.artikel;
 
+------+-----------------+------------+--------+-------------------------------------------+----------------------+---------+-------------------+------+---------+----------+------------+-------------+
 
| id   | select_type     | table      | type   | possible_keys                             | key                  | key_len | ref               | rows | r_rows  | filtered | r_filtered | Extra       |
 
+------+-----------------+------------+--------+-------------------------------------------+----------------------+---------+-------------------+------+---------+----------+------------+-------------+
 
|    1 | PRIMARY         | t1         | index  | NULL                                      | PRIMARY              | 42      | NULL              | 1000 | 1000.00 |   100.00 |     100.00 | Using index |
 
|    1 | PRIMARY         | <derived2> | ref    | key0                                      | key0                 | 43      | test.t1.id        |    2 |    1.00 |   100.00 |     100.00 |             |
 
|    2 | LATERAL DERIVED | t3         | ref    | IDX_3062FD0DA4375C33,IDX_3062FD0DAEA34913 | IDX_3062FD0DA4375C33 | 43      | test.t1.id        |    1 |    1.00 |   100.00 |     100.00 | Using where |
 
|    2 | LATERAL DERIVED | t2         | eq_ref | PRIMARY                                   | PRIMARY              | 4       | test.t3.reference |    1 |    1.00 |   100.00 |     100.00 | Using where |
 
+------+-----------------+------------+--------+-------------------------------------------+----------------------+---------+-------------------+------+---------+----------+------------+-------------+
 
4 rows in set (0.029 sec)
 
 
MariaDB [test]> INSERT INTO t1 SELECT seq FROM seq_1001_to_400000;
 
Query OK, 399000 rows affected (3.912 sec)
 
Records: 399000  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> ANALYZE
 
    -> SELECT * FROM t1
 
    ->   LEFT JOIN
 
    ->   (SELECT t3.artikel, GROUP_CONCAT(t2.reference)
 
    ->    FROM t3 INNER JOIN t2 ON t3.reference = t2.id
 
    ->    GROUP BY t3.artikel) AS x
 
    ->   ON t1.id = x.artikel;
 
+------+-------------+------------+--------+-------------------------------------------+---------+---------+-------------------+--------+-----------+----------+------------+-----------------------------+
 
| id   | select_type | table      | type   | possible_keys                             | key     | key_len | ref               | rows   | r_rows    | filtered | r_filtered | Extra                       |
 
+------+-------------+------------+--------+-------------------------------------------+---------+---------+-------------------+--------+-----------+----------+------------+-----------------------------+
 
|    1 | PRIMARY     | t1         | index  | NULL                                      | PRIMARY | 42      | NULL              | 400000 | 400000.00 |   100.00 |     100.00 | Using index                 |
 
|    1 | PRIMARY     | <derived2> | ref    | key0                                      | key0    | 43      | test.t1.id        |     10 |      0.00 |   100.00 |     100.00 |                             |
 
|    2 | DERIVED     | t3         | ALL    | IDX_3062FD0DA4375C33,IDX_3062FD0DAEA34913 | NULL    | NULL    | NULL              |   1000 |   1000.00 |   100.00 |     100.00 | Using where; Using filesort |
 
|    2 | DERIVED     | t2         | eq_ref | PRIMARY                                   | PRIMARY | 4       | test.t3.reference |      1 |      1.00 |   100.00 |     100.00 | Using where                 |
 
+------+-------------+------------+--------+-------------------------------------------+---------+---------+-------------------+--------+-----------+----------+------------+-----------------------------+
 
4 rows in set (2.725 sec)

the same query on 10.3.2 with "split_grouping_derived=on" takes 6s vs 2.7s on 10.3.4 (or with split_grouping_derived=off )

MariaDB [test]> INSERT INTO t1 SELECT seq FROM seq_1001_to_400000;
 
Query OK, 399000 rows affected (4.206 sec)
 
Records: 399000  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> ANALYZE
 
    -> SELECT * FROM t1
 
    ->   LEFT JOIN
 
    ->   (SELECT t3.artikel, GROUP_CONCAT(t2.reference)
 
    ->    FROM t3 INNER JOIN t2 ON t3.reference = t2.id
 
    ->    GROUP BY t3.artikel) AS x
 
    ->   ON t1.id = x.artikel;
 
+------+-----------------+------------+--------+-------------------------------------------+----------------------+---------+-------------------+--------+-----------+----------+------------+-------------+
 
| id   | select_type     | table      | type   | possible_keys                             | key                  | key_len | ref               | rows   | r_rows    | filtered | r_filtered | Extra       |
 
+------+-----------------+------------+--------+-------------------------------------------+----------------------+---------+-------------------+--------+-----------+----------+------------+-------------+
 
|    1 | PRIMARY         | t1         | index  | NULL                                      | PRIMARY              | 42      | NULL              | 400000 | 400000.00 |   100.00 |     100.00 | Using index |
 
|    1 | PRIMARY         | <derived2> | ref    | key0                                      | key0                 | 43      | test.t1.id        |     10 |      0.00 |   100.00 |     100.00 |             |
 
|    2 | LATERAL DERIVED | t3         | ref    | IDX_3062FD0DA4375C33,IDX_3062FD0DAEA34913 | IDX_3062FD0DA4375C33 | 43      | test.t1.id        |      1 |      0.00 |   100.00 |     100.00 | Using where |
 
|    2 | LATERAL DERIVED | t2         | eq_ref | PRIMARY                                   | PRIMARY              | 4       | test.t3.reference |      1 |      1.00 |   100.00 |     100.00 | Using where |
 
+------+-----------------+------------+--------+-------------------------------------------+----------------------+---------+-------------------+--------+-----------+----------+------------+-------------+
 
4 rows in set (6.201 sec)

Comment by Matt [ 2020-01-03 ]

I am also suffering from this issue (MariaDB 10.4.10). In my case query performance improves from 7.9s to 1.7s by setting split_materialized=off.

In my case, "number of groups in derived table with GROUP BY" = 644603 and "number of rows in the left operand of the LEFT JOIN that have matches in the right operand" = 644603.

Comment by Matt [ 2020-01-06 ]

Would you consider re-opening this issue if I can continue to provide debug information as necessary?

thanks

Comment by Igor Babaev [ 2020-01-06 ]

Matt,
So far I don't see any bug report from you.
A good bug report should contain a test case.
Regards,
Igor.

Generated at Thu Feb 08 08:20:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.