Description:
Hi,
The following two statements use very different plans:
mysql> EXPLAIN SELECT * FROM t WHERE (name LIKE 'adfgjlskdm%' OR email LIKE 'adfgjlskdm%');
+----+-------------+-------+-------------+---------------+-------+---------+------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+---------------+-------+---------+------+------+--------------------------------------+
| 1 | SIMPLE | t | index_merge | t1,t2 | t1,t2 | 767,768 | NULL | 2 | Using sort_union(t1,t2); Using where |
+----+-------------+-------+-------------+---------------+-------+---------+------+------+--------------------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM t WHERE (name LIKE 'adfgjlskdm%' OR email LIKE 'adfgjlskdm%')
-> AND email != '' AND email IS NOT NULL;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | ALL | t1,t2 | NULL | NULL | NULL | 100234 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
How to repeat:
Attached script to create data.
Suggested fix:
index_merge scan should be used in both cases.
Description: Hi, The following two statements use very different plans: mysql> EXPLAIN SELECT * FROM t WHERE (name LIKE 'adfgjlskdm%' OR email LIKE 'adfgjlskdm%'); +----+-------------+-------+-------------+---------------+-------+---------+------+------+--------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+---------------+-------+---------+------+------+--------------------------------------+ | 1 | SIMPLE | t | index_merge | t1,t2 | t1,t2 | 767,768 | NULL | 2 | Using sort_union(t1,t2); Using where | +----+-------------+-------+-------------+---------------+-------+---------+------+------+--------------------------------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM t WHERE (name LIKE 'adfgjlskdm%' OR email LIKE 'adfgjlskdm%') -> AND email != '' AND email IS NOT NULL; +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | t | ALL | t1,t2 | NULL | NULL | NULL | 100234 | Using where | +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) How to repeat: Attached script to create data. Suggested fix: index_merge scan should be used in both cases.