Very slow query on MariaDB 10.3 compared to MySQL 5.5.50

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.