Very slow query on MariaDB 10.3 compared to MySQL 5.5.50

Question

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.

how large are your tables? key_buffer_size looks small, is it possible that new server storage is slower than old one? — NikitaSerbskiy, Commented Dec 3, 2020 at 18:30
Thanks for your comment Nikita. The key_buffer_size is set to just "16777216" on the old server. Even smaller! — Fabrizio Ferrari, Commented Dec 3, 2020 at 19:14
yes, and it means your tables may not fit into the buffer pool on both servers, and if new storage is slower then it affects the query performance more — NikitaSerbskiy, Commented Dec 3, 2020 at 19:17
you have too many keys, often combined keys are more efficient, second use proper joins instead of antiquated comma separated table — nbk, Commented Dec 3, 2020 at 19:23
@NikitaSerbskiy Thanks Nikita, that makes sesne to me... but then my next natural question is: Why is performing so well anyway on the old server? — Fabrizio Ferrari, Commented Dec 3, 2020 at 22:01

Rick James · Accepted Answer · 2020-12-04 02:45:04Z

0

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.)

edited Dec 4, 2020 at 2:45

answered Dec 4, 2020 at 2:27

Rick James

79.4k5 gold badges51 silver badges117 bronze badges

Thank you Rick. I'll try what you have suggested and report!
– Fabrizio Ferrari
Commented Dec 4, 2020 at 21:23
Ok, here are my results: by leaving those table as MyISAM, I get an astonishing improvement by just using your suggested query "select a.* from HLAsset a where a.excluded=0 AND a.date_deleted='' AND a.filetype='SCORCH' AND EXISTS (SELECT 1 FROM HLAssetCategory WHERE asset_id = a.asset_id)" which takes less than 4 seconds to complete. Strangely enough, once I have switched both tables to InnoDB the performance dropped to less than 6 seconds. I tried to add compound indexes on the compared columns but made no difference. Your thoughts on that?
– Fabrizio Ferrari
Commented Dec 4, 2020 at 23:15
@FabrizioFerrari - If you remove the AND EXISTS(...) do you get the 'correct' results? Or do you get too many rows? (And what is the speed like?)
– Rick James
Commented Dec 5, 2020 at 1:08
Well, that's even weirdier... it is taking longer without the AND EXISTS(...)!! Well over 11 seconds! Why's that? Doesn't make any sense to me... that with InnoDB tables. on MyISAM I get less than 3 seconds without the AND EXISTS(...)... awaiting for your thoughts!
– Fabrizio Ferrari
Commented Dec 5, 2020 at 19:44
@FabrizioFerrari - But does it deliver too many rows? Perhaps the sheer quantity of rolls is causing the 11 seconds?
– Rick James
Commented Dec 5, 2020 at 21:35

| Show 13 more comments

Stack Exchange Network

Very slow query on MariaDB 10.3 compared to MySQL 5.5.50

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
mariadb
mariadb-10.3
or ask your own question.

Linked

Hot Network Questions

Very slow query on MariaDB 10.3 compared to MySQL 5.5.50

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqlmariadbmariadb-10.3 or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
mariadb
mariadb-10.3
or ask your own question.