Mysql – Very slow query on MariaDB 10.3 compared to MySQL 5.5.50

mariadbmariadb-10.3MySQL

I already discussed how much slower performance I have experienced on MariaDB 10.3 compared to MySQL 5.5 for some queries in my previously posted thread, but I am now struggling with a specific kind of queries that give me an extremely slow result I can't figure out, even if indexes are added to all involved columns.

The query I am experimenting with right now is the following:

select  distinct a.asset_id, a.*
    from  HLAsset a,
          HLAssetCategory c
    where  a.asset_id=c.asset_id
      and  a.filetype='SCORCH'
      and  a.date_deleted=''
      and  a.excluded=0;

It requests distinct IDs from two pretty big tables. The first table HLAsset includes around 303,000 rows with 31 columns each. The second table HLAssetCategory includes around 627,000 rows with just 2 columns each.

On the old server with MySQL 5.5.50 the query takes around 8 seconds to complete. On the new MariaDB 10.3 it takes well over 22 seconds (!!!), and I can't figure out why.

If anyone of you could help me to understand why such a huge difference in performance, please, let me know. Keep in mind that the new MariaDB is on a much newer and faster server, which makes the whole issue even weirder.

Here is some info for you:

Explain of the query on the old server with MySQL 5.5.50:

mysql> explain select distinct a.asset_id, a.* from HLAsset a, HLAssetCategory c where a.asset_id=c.asset_id and a.filetype='SCORCH' and a.date_deleted='' and a.excluded=0;
+----+-------------+-------+------+----------------------------------------+---------+---------+------------------------------+--------+------------------------------+
| id | select_type | table | type | possible_keys                          | key     | key_len | ref                          | rows   | Extra                        |
+----+-------------+-------+------+----------------------------------------+---------+---------+------------------------------+--------+------------------------------+
|  1 | SIMPLE      | a     | ALL  | PRIMARY,date_deleted,excluded,filetype | NULL    | NULL    | NULL                         | 303211 | Using where; Using temporary |
|  1 | SIMPLE      | c     | ref  | PRIMARY                                | PRIMARY | 34      | mydb.a.asset_id |      2 | Using index; Distinct        |
+----+-------------+-------+------+----------------------------------------+---------+---------+------------------------------+--------+------------------------------+
2 rows in set (0.00 sec)

Explain of the query on the new server with MariaDB 10.3:

MariaDB [virtualsheetmusic]> explain select distinct a.asset_id, a.* from HLAsset a, HLAssetCategory c where a.asset_id=c.asset_id and a.filetype='SCORCH' and a.date_deleted='' and a.excluded=0;
+------+-------------+-------+------+----------------------------------------+---------+---------+------------------------------+--------+------------------------------+
| id   | select_type | table | type | possible_keys                          | key     | key_len | ref                          | rows   | Extra                        |
+------+-------------+-------+------+----------------------------------------+---------+---------+------------------------------+--------+------------------------------+
|    1 | SIMPLE      | a     | ALL  | PRIMARY,date_deleted,excluded,filetype | NULL    | NULL    | NULL                         | 303366 | Using where; Using temporary |
|    1 | SIMPLE      | c     | ref  | PRIMARY                                | PRIMARY | 34      | mydb.a.asset_id |      2 | Using index; Distinct        |
+------+-------------+-------+------+----------------------------------------+---------+---------+------------------------------+--------+------------------------------+
2 rows in set (0.003 sec)

And here is a SHOW CREATE TABLE of the first table HLAsset on the old server with MySQL 5.5.50:

mysql> show create table HLAsset;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HLAsset | CREATE TABLE `HLAsset` (
  `asset_id` varchar(32) NOT NULL,
  `asset_type` varchar(64) DEFAULT NULL,
  `format` varchar(64) DEFAULT NULL,
  `asset_title` varchar(255) DEFAULT NULL,
  `description` text,
  `date_added` varchar(32) DEFAULT NULL,
  `date_deleted` varchar(32) DEFAULT NULL,
  `page_count` int(11) DEFAULT NULL,
  `retail_price` decimal(9,2) DEFAULT NULL,
  `filename` varchar(255) DEFAULT NULL,
  `filetype` varchar(64) DEFAULT NULL,
  `filesource` varchar(32) DEFAULT NULL,
  `mime_type` varchar(64) DEFAULT NULL,
  `fileurl` varchar(256) DEFAULT NULL,
  `world_rights` enum('true','false') DEFAULT NULL,
  `song_number` int(11) DEFAULT NULL,
  `public_domain` enum('true','false') DEFAULT NULL,
  `package_type` varchar(32) DEFAULT NULL,
  `asset_action` varchar(32) DEFAULT NULL,
  `asset_voicing` varchar(256) DEFAULT NULL,
  `performance_time` varchar(256) DEFAULT NULL,
  `external_ref` varchar(30) DEFAULT NULL,
  `difficulty_level_low` decimal(9,2) DEFAULT NULL,
  `difficulty_level_high` decimal(9,2) DEFAULT NULL,
  `tempo` int(11) NOT NULL DEFAULT '0',
  `minimum_quantity` int(11) NOT NULL DEFAULT '0',
  `image_url` varchar(256) DEFAULT NULL,
  `renderingPDF` tinyint(4) NOT NULL,
  `renderingXML` tinyint(4) NOT NULL,
  `extra_score` tinyint(4) NOT NULL,
  `excluded` tinyint(4) NOT NULL,
  PRIMARY KEY (`asset_id`),
  KEY `asset_type` (`asset_type`),
  KEY `date_deleted` (`date_deleted`),
  KEY `world_rights` (`world_rights`),
  KEY `format` (`format`),
  KEY `page_count` (`page_count`),
  KEY `renderingXML` (`renderingXML`),
  KEY `date_added` (`date_added`),
  KEY `tempo` (`tempo`),
  KEY `asset_title` (`asset_title`),
  KEY `excluded` (`excluded`),
  KEY `extra_score` (`extra_score`),
  KEY `filetype` (`filetype`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

And here is a SHOW CREATE TABLE of the second table HLAssetCategory on the old server with MySQL 5.5.50:

mysql> show create table HLAssetCategory;
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                           |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HLAssetCategory | CREATE TABLE `HLAssetCategory` (
  `asset_id` varchar(32) NOT NULL,
  `category_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`asset_id`,`category_id`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

And here is a SHOW CREATE TABLE of the first table HLAsset on the new server with MariaDB 10.3:

MariaDB [virtualsheetmusic]> show create table HLAsset;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HLAsset | CREATE TABLE `HLAsset` (
  `asset_id` varchar(32) NOT NULL,
  `asset_type` varchar(64) DEFAULT NULL,
  `format` varchar(64) DEFAULT NULL,
  `asset_title` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `date_added` varchar(32) DEFAULT NULL,
  `date_deleted` varchar(32) DEFAULT NULL,
  `page_count` int(11) DEFAULT NULL,
  `retail_price` decimal(9,2) DEFAULT NULL,
  `filename` varchar(255) DEFAULT NULL,
  `filetype` varchar(64) DEFAULT NULL,
  `filesource` varchar(32) DEFAULT NULL,
  `mime_type` varchar(64) DEFAULT NULL,
  `fileurl` varchar(256) DEFAULT NULL,
  `world_rights` enum('true','false') DEFAULT NULL,
  `song_number` int(11) DEFAULT NULL,
  `public_domain` enum('true','false') DEFAULT NULL,
  `package_type` varchar(32) DEFAULT NULL,
  `asset_action` varchar(32) DEFAULT NULL,
  `asset_voicing` varchar(256) DEFAULT NULL,
  `performance_time` varchar(256) DEFAULT NULL,
  `external_ref` varchar(30) DEFAULT NULL,
  `difficulty_level_low` decimal(9,2) DEFAULT NULL,
  `difficulty_level_high` decimal(9,2) DEFAULT NULL,
  `tempo` int(11) NOT NULL DEFAULT 0,
  `minimum_quantity` int(11) NOT NULL DEFAULT 0,
  `image_url` varchar(256) DEFAULT NULL,
  `renderingPDF` tinyint(4) NOT NULL,
  `renderingXML` tinyint(4) NOT NULL,
  `extra_score` tinyint(4) NOT NULL,
  `excluded` tinyint(4) NOT NULL,
  PRIMARY KEY (`asset_id`),
  KEY `asset_type` (`asset_type`),
  KEY `date_deleted` (`date_deleted`),
  KEY `world_rights` (`world_rights`),
  KEY `format` (`format`),
  KEY `page_count` (`page_count`),
  KEY `renderingXML` (`renderingXML`),
  KEY `date_added` (`date_added`),
  KEY `tempo` (`tempo`),
  KEY `asset_title` (`asset_title`),
  KEY `excluded` (`excluded`),
  KEY `extra_score` (`extra_score`),
  KEY `filetype` (`filetype`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

And here is a SHOW CREATE TABLE of the second table HLAssetCategory on the new server with MariaDB 10.3:

MariaDB [virtualsheetmusic]> show create table HLAssetCategory;
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                         |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HLAssetCategory | CREATE TABLE `HLAssetCategory` (
  `asset_id` varchar(32) NOT NULL,
  `category_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`asset_id`,`category_id`),
  KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

And finally, here is the my.cnf on the old server with MySQL 5.5.50 (I have removed the comments):

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address        = 0.0.0.0

key_buffer      = 16M

thread_stack        = 192K
thread_cache_size       = 8


myisam-recover         = BACKUP

query_cache_limit   = 1M
query_cache_size        = 16M


expire_logs_days    = 10
max_binlog_size         = 100M

symbolic-links=0
max_allowed_packet=1000M
net_buffer_length=100M
read_buffer_size=16M
max_connections=300
query-cache-type=1
query-cache-size=20M


myisam_recover_options=OFF
thread_stack=262144




innodb_buffer_pool_size=1GB
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 2




[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]


[isamchk]
key_buffer      = 16M

And here is the same file on the new server with MariaDB 10.3:

[server]

character-set-server  = latin1
collation-server      = latin1_swedish_c

[mysqld]
open_files_limit = 65535

user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
port                   = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
skip-external-locking


sql_mode = 'NO_ENGINE_SUBSTITUTION'

bind-address = 0.0.0.0

key_buffer_size        = 20M

thread_stack           = 192K
thread_cache_size      = 200


myisam_recover_options = BACKUP

query_cache_limit      = 1M


tmp_table_size = 64M
max_heap_table_size = 64M


log_error = /var/log/mysql/error.log


slow_query_log
slow_query_log_file    = /var/log/mysql/mariadb-slow.log
long_query_time        = 10
log_slow_rate_limit    = 1000

log_queries_not_using_indexes=ON

expire_logs_days        = 10
max_binlog_size        = 100M

character-set-server  = latin1
collation-server      = latin1_swedish_ci

symbolic-links=0

max_allowed_packet=160M
net_buffer_length=100M
read_buffer_size=16M
max_connections=300
query_cache_type=1
query_cache_size=20M


myisam_recover_options=OFF
thread_stack=262144

innodb_buffer_pool_size=10GB

innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M

innodb_thread_sleep_delay = 0
innodb_file_per_table = 1
skip-name-resolve


innodb_strict_mode = 0


innodb_lru_scan_depth=100
innodb_change_buffer_max_size = 50
read_buffer_size = 256K
innodb_io_capacity = 3900

innodb_flush_neighbors = 0

[embedded]

[mariadb]


[mariadb-10.3]

Eager to know your thoughts about all this.

Thank you in advance to everyone again!

All the best, Fab.

Best Answer

Why did you mention c when you don't seem to need any of its columns. Maybe the solution is simply

from  HLAsset a, HLAssetCategory c

->

from  HLAsset a

OK, the JOIN to c is checking that there is at least one matching row. You may be able to toss the costly DISTINCT if you get rid of the JOIN c and add WHERE ... AND EXISTS ( SELECT 1 FROM HLAssetCategory WHERE asset_id = a.asset_id )

Meanwhile, the DISTINCT goes to the extreme effort of undoing the JOIN -- Going from all the rows in c down to one.

This is likely to help:

a:  (excluded, date_deleted, filetype, asset_id)

Do change from MyISAM to InnoDB. Until then, use key_buffer_size set to about 20% of available RAM.

Other issues...

  • Use DATE or DATETIME for dates, not VARCHAR.
  • Avoid UUIDs and MD5s, especially if they are involved in indexing or JOINing.
  • Learn about the benefits of composite indexes. (It is part of the solution here.)
Related Question