Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4
Description
(I am using 10.4 for debugging but this most likely affects earlier versions too)
Use the attached file to fill the dataset and then we get this table
CREATE TABLE orders ( |
id int(10) unsigned NOT NULL AUTO_INCREMENT, |
customer_id int(10) unsigned DEFAULT NULL, |
is_processed tinyint(3) unsigned DEFAULT NULL, |
 |
PRIMARY KEY (id), |
KEY customer_id (customer_id), |
KEY is_processed (is_processed) |
) ENGINE=InnoDB;
|
The query
SELECT COUNT(id) |
FROM |
ordersis_processed = 1 AND |
customer_id = 10000;
|
Produces this query plan:
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: orders
|
type: index_merge
|
possible_keys: customer_id,is_processed
|
key: customer_id,is_processed
|
key_len: 5,2
|
ref: NULL
|
rows: 9401
|
Extra: Using intersect(customer_id,is_processed); Using where; Using index
|
This is much slower than just doing a ref access on customer_id.
Data distribution for is_processed is as follows
+--------------+---------------------+
|
| is_processed | count(is_processed) |
|
+--------------+---------------------+
|
| 0 | 10542 |
|
| 1 | 1038034 |
|
+--------------+---------------------+
|
so the condition "is_processed=1" has 99% selectivity.
Need to investigate, why does the optimizer choose to use it for index_merge?