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
– 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
– 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
– 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
– 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
– Fabrizio Ferrari, Commented Dec 3, 2020 at 22:01

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

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

Thank you Rick. I'll try what you have suggested and report! — Fabrizio Ferrari
– 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
– 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
– 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
– 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
– Rick James, Commented Dec 5, 2020 at 21:35

Stack Exchange Network

Very slow query on MariaDB 10.3 compared to MySQL 5.5.50

1 Answer 1

Linked

Hot Network Questions

Very slow query on MariaDB 10.3 compared to MySQL 5.5.50

1 Answer 1

Linked

Related

Hot Network Questions