mysql 运算符效率_OR运算符中的MySQL性能

This is my table:

CREATE TABLE `e_relationship` (

`OID` int(11) NOT NULL AUTO_INCREMENT,

`E_E_OID` int(11) NOT NULL,

`E_E_OID2` int(11) NOT NULL,

`REL_DISPLAY` text NOT NULL,

`APP_OID` int(11) NOT NULL,

`META_OID` int(11) NOT NULL,

`STORE_DATE` datetime NOT NULL,

`UID` int(11) DEFAULT NULL,

PRIMARY KEY (`OID`),

KEY `Left_Entity` (`E_E_OID`),

KEY `Right_Entity` (`E_E_OID2`),

KEY `Meta_Left` (`META_OID`,`E_E_OID`),

KEY `Meta_Right` (`META_OID`,`E_E_OID2`)

) ENGINE=InnoDB AUTO_INCREMENT=310169 DEFAULT CHARSET=utf8;

The following query takes about 2.5-3ms, the result set is 1,290 rows, and the total number of rows in the table is 1,008,700:

SELECT * FROM e_relationship WHERE e_e_oid=@value1 OR e_e_oid2=@value1

This is result of EXPLAIN:

id: 1

select_type: SIMPLE

table: e_relationship

type: index_merge

possible_keys: Left_Entity,Right_Entity

key: Left_Entity,Right_Entity

key_len: 4,4

ref: NULL

rows: 1290

Extra: Using union(Left_Entity,Right_Entity); Using where

I would like to speed up this query as this is being quite critical in my system, I'm not sure if I'm reaching some sort of bottleneck in mysql as the number of records has already passed one million, and would like to know about other possible strategies to improve performance.

解决方案

Sometimes MySQL has trouble optimizing OR queries. In this case, you can split it up into two queries using UNION:

SELECT * FROM relationship WHERE e_e_oid = @value1

UNION

SELECT * FROM relationship WHERE e_e_oid2 = @value2

Each subquery will make use of the appropriate index, and then the results will be merged.

However, in simple cases MySQL can automatically perform this transformation, and it's doing so in your query. That's what Using union in the EXPLAIN output means.