|
UPDATE: I have confirmed that this is caused by innodb_stats_method=nulls_ignored, but I am leaving the original description in place.
I have stumbled over something that might possibly be a regression or bug, but I am not completely sure
since there are some config differences between the servers too, but nothing that I think should matter here.
A database has been dumped and imported on a new server.
Old server MariaDB 10.1.36
New server MariaDB 10.2.18 (Same problem originally seen in 10.2.15, upgraded to latest to eliminate causes)
A query run on the old server will complete in less than 2 sec.
On the new server, the same query takes 65-135 sec.
Tables, indexes and data are identical.
Index cardinality seems to be wrong on the new server.
The new server chooses to use a sub-optimal index, and seems to do a filesort and full scan of one of the tables.
Forcing the new server to choose the same index as the old one uses, brings the time down on the new server as well.
Query in question
Original query (Returns 17 rows):
SELECT mts9_users.ID,mts9_users.user_login,mts9_users.display_name FROM mts9_users INNER JOIN mts9_usermeta ON ( mts9_users.ID = mts9_usermeta.user_id )
|
WHERE 1=1 AND ( ( mts9_usermeta.meta_key = 'mts9_user_level' AND mts9_usermeta.meta_value != '0' ) ) ORDER BY display_name ASC;
|
Minimal query showing the problem (Returns 520k rows):
SELECT mts9_users.ID,mts9_users.user_login FROM mts9_users INNER JOIN mts9_usermeta ON ( mts9_users.ID = mts9_usermeta.user_id )
|
WHERE mts9_usermeta.meta_key = 'mts9_user_level' ORDER BY display_name ASC;
|
Query used in tests below (Returns 17 rows):
SELECT mts9_users.ID,mts9_users.user_login FROM mts9_users INNER JOIN mts9_usermeta ON ( mts9_users.ID = mts9_usermeta.user_id )
|
WHERE mts9_usermeta.meta_key = 'mts9_user_level' AND mts9_usermeta.meta_value != '0' ORDER BY display_name ASC;
|
Query Explain on new server
MariaDB [XX]> explain extended SELECT mts9_users.ID,mts9_users.user_login FROM mts9_users INNER JOIN mts9_usermeta ON ( mts9_users.ID = mts9_usermeta.user_id )
|
WHERE mts9_usermeta.meta_key = 'mts9_user_level' AND mts9_usermeta.meta_value != '0' ORDER BY display_name ASC;
|
+------+-------------+---------------+------+------------------+---------+---------+--------------------------+--------+----------+----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+---------------+------+------------------+---------+---------+--------------------------+--------+----------+----------------+
|
| 1 | SIMPLE | mts9_users | ALL | PRIMARY | NULL | NULL | NULL | 515801 | 100.00 | Using filesort |
|
| 1 | SIMPLE | mts9_usermeta | ref | user_id,meta_key | user_id | 8 | XX.mts9_users.ID | 1 | 100.00 | Using where |
|
+------+-------------+---------------+------+------------------+---------+---------+--------------------------+--------+----------+----------------+
|
Query takes 2min 3sec to run
Query Explain on old server
MariaDB [XX]> explain extended SELECT mts9_users.ID,mts9_users.user_login FROM mts9_users INNER JOIN mts9_usermeta ON ( mts9_users.ID = mts9_usermeta.user_id )
|
WHERE mts9_usermeta.meta_key = 'mts9_user_level' AND mts9_usermeta.meta_value != '0' ORDER BY display_name ASC;
|
+------+-------------+---------------+--------+------------------+----------+---------+---------------------------------------------+--------+----------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+---------------+--------+------------------+----------+---------+---------------------------------------------+--------+----------+----------------------------------------------+
|
| 1 | SIMPLE | mts9_usermeta | ref | user_id,meta_key | meta_key | 767 | const | 919672 | 100.00 | Using where; Using temporary; Using filesort |
|
| 1 | SIMPLE | mts9_users | eq_ref | PRIMARY | PRIMARY | 8 | XX.mts9_usermeta.user_id | 1 | 100.00 | |
|
+------+-------------+---------------+--------+------------------+----------+---------+---------------------------------------------+--------+----------+----------------------------------------------+
|
Query takes 1.18 sec to run
Table definitions:
CREATE TABLE mts9_usermeta (
|
umeta_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY_KEY,
|
user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0,
|
meta_key varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
|
meta_value longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
ALTER TABLE mts9_usermeta
|
ADD KEY user_id (user_id),
|
ADD KEY meta_key (meta_key(80)) USING BTREE;
|
|
CREATE TABLE mts9_users (
|
ID bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY_KEY,
|
user_login varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
|
user_pass varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
|
user_nicename varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
|
user_email varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
|
user_url varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
|
user_registered datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
|
user_activation_key varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
|
user_status int(11) NOT NULL DEFAULT 0,
|
display_name varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
ALTER TABLE mts9_users
|
ADD KEY user_login_key (user_login),
|
ADD KEY user_nicename (user_nicename),
|
ADD KEY user_email (user_email),
|
ADD KEY idx_display_name (display_name);
|
Old server index cardinality:
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| mts9_usermeta | 0 | PRIMARY | 1 | umeta_id | A | 21474248 | NULL | NULL | | BTREE | | |
|
| mts9_usermeta | 1 | user_id | 1 | user_id | A | 1022583 | NULL | NULL | | BTREE | | |
|
| mts9_usermeta | 1 | meta_key | 1 | meta_key | A | 57264 | 191 | NULL | YES | BTREE | | |
|
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
|
+------------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+------------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| mts9_users | 0 | PRIMARY | 1 | ID | A | 512046 | NULL | NULL | | BTREE | | |
|
| mts9_users | 1 | user_login_key | 1 | user_login | A | 512046 | NULL | NULL | | BTREE | | |
|
| mts9_users | 1 | user_nicename | 1 | user_nicename | A | 512046 | NULL | NULL | | BTREE | | |
|
| mts9_users | 1 | user_email | 1 | user_email | A | 512046 | NULL | NULL | | BTREE | | |
|
+------------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
New server index cardinality
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| mts9_usermeta | 0 | PRIMARY | 1 | umeta_id | A | 22551453 | NULL | NULL | | BTREE | | |
|
| mts9_usermeta | 1 | user_id | 1 | user_id | A | 22551453 | NULL | NULL | | BTREE | | |
|
| mts9_usermeta | 1 | meta_key | 1 | meta_key | A | 22551453 | 191 | NULL | YES | BTREE | | |
|
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
|
+------------+------------+------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+------------+------------+------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| mts9_users | 0 | PRIMARY | 1 | ID | A | 515797 | NULL | NULL | | BTREE | | |
|
| mts9_users | 1 | user_login_key | 1 | user_login | A | 515797 | NULL | NULL | | BTREE | | |
|
| mts9_users | 1 | user_nicename | 1 | user_nicename | A | 515797 | NULL | NULL | | BTREE | | |
|
| mts9_users | 1 | user_email | 1 | user_email | A | 515797 | NULL | NULL | | BTREE | | |
|
+------------+------------+------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
|