MySQL tuning advise: replace the "or" with "union all" in MySQL subquery
MySQL version: 5.5.18
Table info:
[cc lang='sql']root@xf_test 02:33:06>show create table fd_relation\G
*************************** 1. row ***************************
Table: fd_relation
Create Table: CREATE TABLE `fd_relation` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'PK',
`GMT_CREATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`GMT_MODIFIED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`SENDER_ID` bigint(20) DEFAULT NULL,
`RECEIVER_ID` bigint(20) DEFAULT NULL,
`SENDER_STATUS` varchar(20) DEFAULT NULL,
`RECEIVER_STATUS` varchar(20) DEFAULT NULL,
`SEND_INFO` varchar(2000),
PRIMARY KEY (`ID`),
KEY `idx_senderid` (`SENDER_ID`,`SENDER_STATUS`,`RECEIVER_ID`),
KEY `idx_recivesid` (`RECEIVER_ID`,`RECEIVER_STATUS`,`SENDER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2000000 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)[/cc]
This sql use Covering Indexes
[cc lang='sql']root@xf_test 02:52:37>explain select count(*)
-> from fd_relation
-> where (sender_id = 101555804 and SENDER_STATUS = 'default' )\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fd_relation
type: ref
possible_keys: idx_senderid
key: idx_senderid
key_len: 72
ref: const,const
rows: 6
Extra: Using where; Using index
1 row in set (0.00 sec)[/cc]
When we use the "or" for subquery, the MySQL optimizer choose full table SACN :( It is bad performance。
[cc lang='sql']root@xf_test 02:39:55>explain select count(*)
-> from fd_relation
-> where (sender_id = 101555804 and SENDER_STATUS = 'default' and
-> receiver_id != sender_id)
-> or (receiver_id = 101555804 and RECEIVER_STATUS = 'default' and
-> receiver_id != sender_id)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fd_relation
type: ALL
possible_keys: idx_senderid,idx_recivesid
key: NULL
key_len: NULL
ref: NULL
rows: 881071
Extra: Using where
1 row in set (0.00 sec)[/cc]
When using "union all", the MySQL optimizer choose the Covering Indexes for each sql, and "union result" at last.
[cc lang='sql']root@xf_test 02:32:12>explain select sum(aaa)
-> from (select count(*) aaa
-> from fd_relation
-> where (sender_id = 101555804 and SENDER_STATUS = 'default')
-> union all
-> select count(*) aaa
-> from fd_relation
-> where (receiver_id = 101555804 and RECEIVER_STATUS = 'default')) t;
+----+--------------+-------------+------+---------------+---------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------+------+---------------+---------------+---------+------+------+--------------------------+
| 1 | PRIMARY |
| 2 | DERIVED | fd_relation | ref | idx_senderid | idx_senderid | 72 | | 6 | Using where; Using index |
| 3 | UNION | fd_relation | ref | idx_recivesid | idx_recivesid | 72 | | 1 | Using where; Using index |
| NULL | UNION RESULT |
+----+--------------+-------------+------+---------------+---------------+---------+------+------+--------------------------+
4 rows in set (0.00 sec)[/cc]
In some cases, the MySQL optimizer choose the "Using union" for indexes union result, but it is not stabilized.
It will be out of control.
Look before you leap.
[cc lang='sql']root@xf_test 02:26:30>explain select count(*)
-> from fd_relation
-> where (sender_id = 101555804 and SENDER_STATUS = 'default' )
-> or (receiver_id = 101555804 and RECEIVER_STATUS = 'default')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fd_relation
type: index_merge
possible_keys: idx_senderid,idx_recivesid
key: idx_senderid,idx_recivesid
key_len: 72,72
ref: NULL
rows: 7
Extra: Using union(idx_senderid,idx_recivesid); Using where
1 row in set (0.00 sec)[/cc]
--eof
This work is licensed under a CC A-S 4.0 International License.