|
My, what an impressive set of bracketing. For clarity can you include your mysql and mariadb config and:
SHOW CREATE TABLE wp_users;
|
SHOW CREATE TABLE wp_usermeta
|
.
Can you edit the query at all? The unaliased wp_usermeta seems to be just ensuring a sort_name exists for the user.
Does removing the bracking on MariaDB help its speed/query plan?
SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.*
|
FROM wp_users
|
INNER JOIN wp_usermeta
|
ON ( wp_users.id = wp_usermeta.user_id )
|
INNER JOIN wp_usermeta AS mt1
|
ON ( wp_users.id = mt1.user_id )
|
INNER JOIN wp_usermeta AS mt2
|
ON ( wp_users.id = mt2.user_id )
|
INNER JOIN wp_usermeta AS mt3
|
ON ( wp_users.id = mt3.user_id )
|
INNER JOIN wp_usermeta AS mt4
|
ON ( wp_users.id = mt4.user_id )
|
INNER JOIN wp_usermeta AS mt5
|
ON ( wp_users.id = mt5.user_id )
|
WHERE 1 = 1
|
AND wp_usermeta.meta_key = 'sort_name'
|
AND mt1.meta_key = 'is_blocked_persona'
|
AND mt1.meta_value != '1'
|
AND mt2.meta_key = 'is_blocked_manually'
|
AND mt2.meta_value != '1'
|
AND mt3.meta_key = 'is_user_hidden'
|
AND mt3.meta_value != '1'
|
AND ( ( mt4.meta_key = 'occupation'
|
AND mt4.meta_value LIKE 'rene' )
|
OR ( mt5.meta_key = 'first_name'
|
AND mt5.meta_value LIKE 'rene' ) )
|
LIMIT 0, 10;
|
|
|
I've ceated this bug report because MySQL 5.7 seems to make some magic with this query, because MySQL 5.6 is almost as slow as as MariaDB. Your modified query performance is OK.
MariaDB [test]> SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.*
|
-> FROM wp_users
|
-> INNER JOIN wp_usermeta
|
-> ON ( wp_users.id = wp_usermeta.user_id )
|
-> INNER JOIN wp_usermeta AS mt1
|
-> ON ( wp_users.id = mt1.user_id )
|
-> INNER JOIN wp_usermeta AS mt2
|
-> ON ( wp_users.id = mt2.user_id )
|
-> INNER JOIN wp_usermeta AS mt3
|
-> ON ( wp_users.id = mt3.user_id )
|
-> INNER JOIN wp_usermeta AS mt4
|
-> ON ( wp_users.id = mt4.user_id )
|
-> INNER JOIN wp_usermeta AS mt5
|
-> ON ( wp_users.id = mt5.user_id )
|
-> WHERE 1 = 1
|
-> AND wp_usermeta.meta_key = 'sort_name'
|
-> AND mt1.meta_key = 'is_blocked_persona'
|
-> AND mt1.meta_value != '1'
|
-> AND mt2.meta_key = 'is_blocked_manually'
|
-> AND mt2.meta_value != '1'
|
-> AND mt3.meta_key = 'is_user_hidden'
|
-> AND mt3.meta_value != '1'
|
-> AND ( ( mt4.meta_key = 'occupation'
|
-> AND mt4.meta_value LIKE 'rene' )
|
-> OR ( mt5.meta_key = 'first_name'
|
-> AND mt5.meta_value LIKE 'rene' ) )
|
-> LIMIT 0, 10;
|
+------+--------------+------------------------------------+---------------+------------------------+----------+---------------------+---------------------+-------------+--------------+
|
| ID | user_login | user_pass | user_nicename | user_email | user_url | user_registered | user_activation_key | user_status | display_name |
|
+------+--------------+------------------------------------+---------------+------------------------+----------+---------------------+---------------------+-------------+--------------+
|
|REMOVED|
|
+------+--------------+------------------------------------+---------------+------------------------+----------+---------------------+---------------------+-------------+--------------+
|
1 row in set (0.29 sec)
|
MariaDB [test]> SHOW CREATE TABLE wp_users; SHOW CREATE TABLE wp_usermeta;
|
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| wp_users | CREATE TABLE `wp_users` (
|
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`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 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 '',
|
PRIMARY KEY (`ID`),
|
KEY `user_login_key` (`user_login`),
|
KEY `user_nicename` (`user_nicename`),
|
KEY `user_email` (`user_email`)
|
) ENGINE=InnoDB AUTO_INCREMENT=109778 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
|
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| wp_usermeta | CREATE TABLE `wp_usermeta` (
|
`umeta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`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,
|
PRIMARY KEY (`umeta_id`),
|
KEY `user_id` (`user_id`),
|
KEY `meta_key` (`meta_key`(191))
|
) ENGINE=InnoDB AUTO_INCREMENT=1880734 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
|
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
MySQL and MariaDB configs are default, I've made those tests on fresh Centos7 VM-s.
cat /etc/my.cnf.d/server.cnf
#
|
# These groups are read by MariaDB server.
|
# Use it for options that only the server (but not clients) should see
|
#
|
# See the examples of server my.cnf files in /usr/share/mysql/
|
#
|
|
# this is read by the standalone daemon and embedded servers
|
[server]
|
|
# this is only for the mysqld standalone daemon
|
[mysqld]
|
|
#
|
# * Galera-related settings
|
#
|
[galera]
|
# Mandatory settings
|
#wsrep_on=ON
|
#wsrep_provider=
|
#wsrep_cluster_address=
|
#binlog_format=row
|
#default_storage_engine=InnoDB
|
#innodb_autoinc_lock_mode=2
|
#
|
# Allow server to accept connections on all interfaces.
|
#
|
#bind-address=0.0.0.0
|
#
|
# Optional setting
|
#wsrep_slave_threads=1
|
#innodb_flush_log_at_trx_commit=0
|
|
# this is only for embedded server
|
[embedded]
|
|
# This group is only read by MariaDB servers, not by MySQL.
|
# If you use the same .cnf file for MySQL and MariaDB,
|
# you can put MariaDB-only options here
|
[mariadb]
|
|
# This group is only read by MariaDB-10.1 servers.
|
# If you use the same .cnf file for MariaDB of different versions,
|
# use this group for options that older servers don't understand
|
[mariadb-10.1]
|
|
|
Thanks for the added information - it gives a hint as to what code to improve. An explain on the the "enhanced query" would be useful too. How many of the users are blocked in some way? Even 0.35 seconds is a rather slow response for so few rows. Is there a reference to the wordpress code that generated this awful schema/query?
|
|
Sorry for late answer.
> explain SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.*
|
-> FROM wp_users
|
-> INNER JOIN wp_usermeta
|
-> ON ( wp_users.id = wp_usermeta.user_id )
|
-> INNER JOIN wp_usermeta AS mt1
|
-> ON ( wp_users.id = mt1.user_id )
|
-> INNER JOIN wp_usermeta AS mt2
|
-> ON ( wp_users.id = mt2.user_id )
|
-> INNER JOIN wp_usermeta AS mt3
|
-> ON ( wp_users.id = mt3.user_id )
|
-> INNER JOIN wp_usermeta AS mt4
|
-> ON ( wp_users.id = mt4.user_id )
|
-> INNER JOIN wp_usermeta AS mt5
|
-> ON ( wp_users.id = mt5.user_id )
|
-> WHERE 1 = 1
|
-> AND wp_usermeta.meta_key = 'sort_name'
|
-> AND mt1.meta_key = 'is_blocked_persona'
|
-> AND mt1.meta_value != '1'
|
-> AND mt2.meta_key = 'is_blocked_manually'
|
-> AND mt2.meta_value != '1'
|
-> AND mt3.meta_key = 'is_user_hidden'
|
-> AND mt3.meta_value != '1'
|
-> AND ( ( mt4.meta_key = 'occupation'
|
-> AND mt4.meta_value LIKE 'rene' )
|
-> OR ( mt5.meta_key = 'first_name'
|
-> AND mt5.meta_value LIKE 'rene' ) )
|
-> LIMIT 0, 10;
|
+------+-------------+-------------+--------+------------------+----------+---------+--------------------------+------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------------+--------+------------------+----------+---------+--------------------------+------+------------------------------+
|
| 1 | SIMPLE | wp_usermeta | ref | user_id,meta_key | meta_key | 767 | const | 373 | Using where; Using temporary |
|
| 1 | SIMPLE | wp_users | eq_ref | PRIMARY | PRIMARY | 8 | test.wp_usermeta.user_id | 1 | |
|
| 1 | SIMPLE | mt2 | ref | user_id,meta_key | user_id | 8 | test.wp_usermeta.user_id | 13 | Using where; Distinct |
|
| 1 | SIMPLE | mt3 | ref | user_id,meta_key | user_id | 8 | test.wp_usermeta.user_id | 13 | Using where; Distinct |
|
| 1 | SIMPLE | mt1 | ref | user_id,meta_key | user_id | 8 | test.wp_usermeta.user_id | 13 | Using where; Distinct |
|
| 1 | SIMPLE | mt4 | ref | user_id,meta_key | user_id | 8 | test.wp_usermeta.user_id | 13 | Distinct |
|
| 1 | SIMPLE | mt5 | ref | user_id,meta_key | user_id | 8 | test.wp_usermeta.user_id | 13 | Using where; Distinct |
|
+------+-------------+-------------+--------+------------------+----------+---------+--------------------------+------+------------------------------+
|
7 rows in set (0.01 sec)
|
|
|
BB, yes, example data would be really nice to have. If you still have it, can you please upload it?
|
|
BB, thanks for the data dump!
My results:
With MariaDB 10.2-current-debug, the query takes 1 min. 40 sec and the EXPLAIN is:
+------+-------------+-------------+--------+------------------+----------+---------+-------------------------+------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------------+--------+------------------+----------+---------+-------------------------+------+------------------------------+
|
| 1 | SIMPLE | wp_usermeta | ref | user_id,meta_key | meta_key | 767 | const | 349 | Using where; Using temporary |
|
| 1 | SIMPLE | wp_users | eq_ref | PRIMARY | PRIMARY | 8 | j71.wp_usermeta.user_id | 1 | |
|
| 1 | SIMPLE | mt2 | ref | user_id,meta_key | user_id | 8 | j71.wp_usermeta.user_id | 11 | Using where; Distinct |
|
| 1 | SIMPLE | mt1 | ref | user_id,meta_key | user_id | 8 | j71.wp_usermeta.user_id | 11 | Using where; Distinct |
|
| 1 | SIMPLE | mt3 | ref | user_id,meta_key | user_id | 8 | j71.wp_usermeta.user_id | 11 | Distinct |
|
| 1 | SIMPLE | mt4 | ref | user_id,meta_key | user_id | 8 | j71.wp_usermeta.user_id | 11 | Distinct |
|
| 1 | SIMPLE | mt5 | ref | user_id,meta_key | user_id | 8 | j71.wp_usermeta.user_id | 11 | Using where; Distinct |
|
+------+-------------+-------------+--------+------------------+----------+---------+-------------------------+------+------------------------------+
|
With MySQL 5.7.18-debug, the query runs 5.82 sec and the EXPLAIN is:
+----+-------------+-------------+------------+--------+------------------+----------+---------+-----------------+------+----------+------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------------+------------+--------+------------------+----------+---------+-----------------+------+----------+------------------------------+
|
| 1 | SIMPLE | mt1 | NULL | ref | user_id,meta_key | meta_key | 767 | const | 349 | 90.00 | Using where; Using temporary |
|
| 1 | SIMPLE | mt2 | NULL | ref | user_id,meta_key | user_id | 8 | j55.mt1.user_id | 23 | 3.78 | Using where |
|
| 1 | SIMPLE | wp_usermeta | NULL | ref | user_id,meta_key | user_id | 8 | j55.mt1.user_id | 23 | 4.19 | Using where |
|
| 1 | SIMPLE | wp_users | NULL | eq_ref | PRIMARY | PRIMARY | 8 | j55.mt1.user_id | 1 | 100.00 | NULL |
|
| 1 | SIMPLE | mt4 | NULL | ref | user_id,meta_key | user_id | 8 | j55.mt1.user_id | 23 | 100.00 | Distinct |
|
| 1 | SIMPLE | mt5 | NULL | ref | user_id,meta_key | user_id | 8 | j55.mt1.user_id | 23 | 100.00 | Using where; Distinct |
|
| 1 | SIMPLE | mt3 | NULL | ref | user_id,meta_key | user_id | 8 | j55.mt1.user_id | 23 | 100.00 | Using where; Distinct |
|
+----+-------------+-------------+------------+--------+------------------+----------+---------+-----------------+------+----------+------------------------------+
|
|
|
Looking at the EXPLAIN outputs, it is not immediately obvious to me what the issue is.
ANALYZE FORMAT=JSON output:
ANALYZE: {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 101711,
|
"temporary_table": {
|
"table": {
|
"table_name": "wp_usermeta",
|
"access_type": "ref",
|
"possible_keys": ["user_id", "meta_key"],
|
"key": "meta_key",
|
"key_length": "767",
|
"used_key_parts": ["meta_key"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 349,
|
"r_rows": 349,
|
"r_total_time_ms": 6.7582,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "wp_usermeta.meta_key = 'sort_name'"
|
},
|
"table": {
|
"table_name": "wp_users",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["ID"],
|
"ref": ["j71.wp_usermeta.user_id"],
|
"r_loops": 349,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 6.2255,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "mt2",
|
"access_type": "ref",
|
"possible_keys": ["user_id", "meta_key"],
|
"key": "user_id",
|
"key_length": "8",
|
"used_key_parts": ["user_id"],
|
"ref": ["j71.wp_usermeta.user_id"],
|
"r_loops": 349,
|
"rows": 11,
|
"r_rows": 23.335,
|
"r_total_time_ms": 158.7,
|
"filtered": 100,
|
"r_filtered": 4.2485,
|
"attached_condition": "mt2.meta_key = 'is_blocked_manually' and mt2.meta_value <> '1'",
|
"distinct": true
|
},
|
"table": {
|
"table_name": "mt1",
|
"access_type": "ref",
|
"possible_keys": ["user_id", "meta_key"],
|
"key": "user_id",
|
"key_length": "8",
|
"used_key_parts": ["user_id"],
|
"ref": ["j71.wp_usermeta.user_id"],
|
"r_loops": 346,
|
"rows": 11,
|
"r_rows": 23.329,
|
"r_total_time_ms": 162.44,
|
"filtered": 100,
|
"r_filtered": 3.89,
|
"attached_condition": "mt1.meta_key = 'is_blocked_persona' and mt1.meta_value <> '1'",
|
"distinct": true
|
},
|
"table": {
|
"table_name": "mt3",
|
"access_type": "ref",
|
"possible_keys": ["user_id", "meta_key"],
|
"key": "user_id",
|
"key_length": "8",
|
"used_key_parts": ["user_id"],
|
"ref": ["j71.wp_usermeta.user_id"],
|
"r_loops": 314,
|
"rows": 11,
|
"r_rows": 23.71,
|
"r_total_time_ms": 142.06,
|
"filtered": 100,
|
"r_filtered": 100,
|
"distinct": true
|
},
|
"table": {
|
"table_name": "mt4",
|
"access_type": "ref",
|
"possible_keys": ["user_id", "meta_key"],
|
"key": "user_id",
|
"key_length": "8",
|
"used_key_parts": ["user_id"],
|
"ref": ["j71.wp_usermeta.user_id"],
|
"r_loops": 7445,
|
"rows": 11,
|
"r_rows": 24.504,
|
"r_total_time_ms": 3440.4,
|
"filtered": 100,
|
"r_filtered": 100,
|
"distinct": true
|
},
|
"table": {
|
"table_name": "mt5",
|
"access_type": "ref",
|
"possible_keys": ["user_id", "meta_key"],
|
"key": "user_id",
|
"key_length": "8",
|
"used_key_parts": ["user_id"],
|
"ref": ["j71.wp_usermeta.user_id"],
|
"r_loops": 182433,
|
"rows": 11,
|
"r_rows": 25.413,
|
"r_total_time_ms": 89814,
|
"filtered": 100,
|
"r_filtered": 2.2e-5,
|
"attached_condition": "mt3.meta_key = 'is_user_hidden' and mt3.meta_value <> '1' and mt4.meta_key = 'occupation' and mt4.meta_value like 'rene' or mt5.meta_key = 'first_name' and mt5.meta_value like 'rene'",
|
"distinct": true
|
}
|
}
|
}
|
}
|
1 row in set (1 min 41.67 sec)
|
|
|
Just the interesting parts:
"r_total_time_ms": 101711,
|
"table": {
|
"table_name": "wp_usermeta",
|
"rows": 349,
|
"r_rows": 349,
|
"r_total_time_ms": 6.7582,
|
"filtered": 100,
|
"r_filtered": 100,
|
},
|
"table": {
|
"table_name": "wp_users",
|
"r_loops": 349,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 6.2255,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "mt2",
|
"r_loops": 349,
|
"rows": 11,
|
"r_rows": 23.335,
|
"r_total_time_ms": 158.7,
|
"filtered": 100,
|
"r_filtered": 4.2485,
|
}
|
"table": {
|
"table_name": "mt1",
|
"r_loops": 346,
|
"rows": 11,
|
"r_rows": 23.329,
|
"r_total_time_ms": 162.44,
|
"filtered": 100,
|
"r_filtered": 3.89,
|
},
|
"table": {
|
"table_name": "mt3",
|
"r_loops": 314,
|
"rows": 11,
|
"r_rows": 23.71,
|
"r_total_time_ms": 142.06,
|
"filtered": 100,
|
"r_filtered": 100,
|
},
|
nothing notable so far....
"table": {
|
"table_name": "mt4",
|
"r_loops": 7445,
|
"rows": 11,
|
"r_rows": 24.504,
|
"r_total_time_ms": 3440.4,
|
"filtered": 100,
|
"r_filtered": 100,
|
},
|
"table": {
|
"table_name": "mt5",
|
"r_loops": 182433,
|
"rows": 11,
|
"r_rows": 25.413,
|
"r_total_time_ms": 89814,
|
"filtered": 100,
|
"r_filtered": 2.2e-5,
|
}
|
So, out of the total of 101 seconds, we spend 89 seconds accessing table mt5.
Query plans in MariaDB:
| 1 | SIMPLE | mt5 | ref | user_id,meta_key | user_id | 8 | j71.wp_usermeta.user_id | 11 | Using where; Distinct |
|
and MySQL
| 1 | SIMPLE | mt5 | ref | user_id,meta_key | user_id | 8 | j55.mt1.user_id | 23 | 100.00 | Using where; Distinct |
|
use the same index.
However MySQL puts the table earlier on the query plan.
|
|
Handler increments from MySQL:
| Handler_read_key | 8883 |
|
| Handler_read_next | 208455 |
|
| Handler_read_rnd_next | 2 |
|
| Handler_write | 1 |
|
from MariaDB:
| Handler_read_key | 191237 |
|
| Handler_read_next | 4842560 |
|
| Handler_read_rnd_next | 2 |
|
| Handler_tmp_write | 1 |
|
|
- After restarting the MariaDB, the query plan is slightly different but mt5 table is still the last and execution time is about the same
- Collecting and using EITS statistics makes filtered match r_filtered, but that has no effect on the query plan:
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 101672,
|
"temporary_table": {
|
"table": {
|
"table_name": "wp_usermeta",
|
"access_type": "ref",
|
"possible_keys": ["user_id", "meta_key"],
|
"key": "meta_key",
|
"key_length": "767",
|
"used_key_parts": ["meta_key"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 349,
|
"r_rows": 349,
|
"r_total_time_ms": 6.8125,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "wp_usermeta.meta_key = 'sort_name'"
|
},
|
"table": {
|
"table_name": "mt1",
|
"access_type": "ref",
|
"possible_keys": ["user_id", "meta_key"],
|
"key": "user_id",
|
"key_length": "8",
|
"used_key_parts": ["user_id"],
|
"ref": ["j71.wp_usermeta.user_id"],
|
"r_loops": 349,
|
"rows": 23,
|
"r_rows": 23.361,
|
"r_total_time_ms": 164.83,
|
"filtered": 4.2298,
|
"r_filtered": 3.8636,
|
"attached_condition": "mt1.meta_key = 'is_blocked_persona' and mt1.meta_value <> '1'"
|
},
|
"table": {
|
"table_name": "mt2",
|
"access_type": "ref",
|
"possible_keys": ["user_id", "meta_key"],
|
"key": "user_id",
|
"key_length": "8",
|
"used_key_parts": ["user_id"],
|
"ref": ["j71.wp_usermeta.user_id"],
|
"r_loops": 315,
|
"rows": 23,
|
"r_rows": 23.79,
|
"r_total_time_ms": 148,
|
"filtered": 4.2298,
|
"r_filtered": 4.19,
|
"attached_condition": "mt2.meta_key = 'is_blocked_manually' and mt2.meta_value <> '1'"
|
},
|
"table": {
|
"table_name": "wp_users",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["ID"],
|
"ref": ["j71.wp_usermeta.user_id"],
|
"r_loops": 314,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 5.6465,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "mt3",
|
"access_type": "ref",
|
"possible_keys": ["user_id", "meta_key"],
|
"key": "user_id",
|
"key_length": "8",
|
"used_key_parts": ["user_id"],
|
"ref": ["j71.wp_usermeta.user_id"],
|
"r_loops": 314,
|
"rows": 23,
|
"r_rows": 23.71,
|
"r_total_time_ms": 144.07,
|
"filtered": 100,
|
"r_filtered": 100,
|
"distinct": true
|
},
|
"table": {
|
"table_name": "mt4",
|
"access_type": "ref",
|
"possible_keys": ["user_id", "meta_key"],
|
"key": "user_id",
|
"key_length": "8",
|
"used_key_parts": ["user_id"],
|
"ref": ["j71.wp_usermeta.user_id"],
|
"r_loops": 7445,
|
"rows": 23,
|
"r_rows": 24.504,
|
"r_total_time_ms": 3478.1,
|
"filtered": 100,
|
"r_filtered": 100,
|
"distinct": true
|
},
|
"table": {
|
"table_name": "mt5",
|
"access_type": "ref",
|
"possible_keys": ["user_id", "meta_key"],
|
"key": "user_id",
|
"key_length": "8",
|
"used_key_parts": ["user_id"],
|
"ref": ["j71.wp_usermeta.user_id"],
|
"r_loops": 182433,
|
"rows": 23,
|
"r_rows": 25.413,
|
"r_total_time_ms": 89813,
|
"filtered": 100,
|
"r_filtered": 2.2e-5,
|
"attached_condition": "mt3.meta_key = 'is_user_hidden' and mt3.meta_value <> '1' and mt4.meta_key = 'occupation' and mt4.meta_value like 'rene' or mt5.meta_key = 'first_name' and mt5.meta_value like 'rene'",
|
"distinct": true
|
}
|
}
|
}
|
}
|
|
anon.dump