| Bug #72745 | Wrong Results When Using Intersect | ||
|---|---|---|---|
| Submitted: | 26 May 2014 4:28 | Modified: | 26 May 2014 5:37 |
| Reporter: | Will Fong | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.5.37, 5.5.38 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | index merge, intersect | ||
[26 May 2014 4:28]
Will Fong
Schema
Attachment: TAB1.sql.bz2 (application/x-bzip, text), 208.87 KiB.
[26 May 2014 5:37]
MySQL Verification Team
Hello Will, Thank you for the bug report and test case. Verified as described. Thanks, Umesh
[26 May 2014 5:45]
MySQL Verification Team
// 5.5.38 - Affected
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.5.38-debug-log |
mysql> SELECT * FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0';
+-------+------+---+---+----+---+----+----+-------+---------------------+-------+---------------------+---+
| A | B | C | D | E | F | G | H | I | J | K | L | M |
+-------+------+---+---+----+---+----+----+-------+---------------------+-------+---------------------+---+
| 1668 | 2014 | K | 2 | 06 | 0 | 20 | 26 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1674 | 2014 | K | 2 | 06 | 0 | 20 | 28 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1680 | 2014 | K | 2 | 06 | 0 | 20 | 30 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31137 | 2014 | K | 2 | 06 | 0 | 20 | 21 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31140 | 2014 | K | 2 | 06 | 0 | 20 | 24 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31142 | 2014 | K | 2 | 06 | 0 | 20 | 26 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31144 | 2014 | K | 2 | 06 | 0 | 20 | 28 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31146 | 2014 | K | 2 | 06 | 0 | 20 | 30 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
+-------+------+---+---+----+---+----+----+-------+---------------------+-------+---------------------+---+
8 rows in set (0.03 sec)
mysql> SELECT COUNT(*) FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0';
+----------+
| COUNT(*) |
+----------+
| 20 |
+----------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: ref
possible_keys: idx_STDT_GR_EXMNT_NO_INF_1,idx_STDT_GR_EXMNT_NO_INF_2,idx_STDT_GR_EXMNT_NO_INF_3,idx_STDT_GR_EXMNT_NO_INF_4,idx_STDT_GR_EXMNT_NO_INF_5,idx_STDT_GR_EXMNT_NO_INF_6,idx_STDT_GR_EXMNT_NO_INF_8,idx_STDT_GR_EXMNT_NO_INF_9,idx_STDT_GR_EXMNT_NO_INF_10,idx_STDT_GR_EXMNT_NO_INF_11
key: idx_STDT_GR_EXMNT_NO_INF_11
key_len: 52
ref: const,const,const,const,const,const,const
rows: 24
Extra: Using where; Using index
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT * FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: index_merge
possible_keys: idx_STDT_GR_EXMNT_NO_INF_1,idx_STDT_GR_EXMNT_NO_INF_2,idx_STDT_GR_EXMNT_NO_INF_3,idx_STDT_GR_EXMNT_NO_INF_4,idx_STDT_GR_EXMNT_NO_INF_5,idx_STDT_GR_EXMNT_NO_INF_6,idx_STDT_GR_EXMNT_NO_INF_8,idx_STDT_GR_EXMNT_NO_INF_9,idx_STDT_GR_EXMNT_NO_INF_10,idx_STDT_GR_EXMNT_NO_INF_11
key: idx_STDT_GR_EXMNT_NO_INF_11,idx_STDT_GR_EXMNT_NO_INF_10,idx_STDT_GR_EXMNT_NO_INF_6,idx_STDT_GR_EXMNT_NO_INF_5
key_len: 52,52,52,36
ref: NULL
rows: 3
Extra: Using intersect(idx_STDT_GR_EXMNT_NO_INF_11,idx_STDT_GR_EXMNT_NO_INF_10,idx_STDT_GR_EXMNT_NO_INF_6,idx_STDT_GR_EXMNT_NO_INF_5); Using where
1 row in set (0.01 sec)
[26 May 2014 5:46]
MySQL Verification Team
// 5.6.19 - Not affected
mysql> select version();
+-------------------------------------------+
| version() |
+-------------------------------------------+
| 5.6.19-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0';
+-------+------+---+---+----+---+----+----+-------+---------------------+-------+---------------------+---+
| A | B | C | D | E | F | G | H | I | J | K | L | M |
+-------+------+---+---+----+---+----+----+-------+---------------------+-------+---------------------+---+
| 1668 | 2014 | K | 2 | 06 | 0 | 20 | 26 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1674 | 2014 | K | 2 | 06 | 0 | 20 | 28 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1680 | 2014 | K | 2 | 06 | 0 | 20 | 30 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31137 | 2014 | K | 2 | 06 | 0 | 20 | 21 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31140 | 2014 | K | 2 | 06 | 0 | 20 | 24 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31142 | 2014 | K | 2 | 06 | 0 | 20 | 26 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31144 | 2014 | K | 2 | 06 | 0 | 20 | 28 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31146 | 2014 | K | 2 | 06 | 0 | 20 | 30 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1653 | 2014 | K | 2 | 06 | 0 | 20 | 21 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1677 | 2014 | K | 2 | 06 | 0 | 20 | 29 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31138 | 2014 | K | 2 | 06 | 0 | 20 | 22 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1656 | 2014 | K | 2 | 06 | 0 | 20 | 22 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1662 | 2014 | K | 2 | 06 | 0 | 20 | 24 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31139 | 2014 | K | 2 | 06 | 0 | 20 | 23 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31141 | 2014 | K | 2 | 06 | 0 | 20 | 25 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31143 | 2014 | K | 2 | 06 | 0 | 20 | 27 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31145 | 2014 | K | 2 | 06 | 0 | 20 | 29 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1659 | 2014 | K | 2 | 06 | 0 | 20 | 23 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1665 | 2014 | K | 2 | 06 | 0 | 20 | 25 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1671 | 2014 | K | 2 | 06 | 0 | 20 | 27 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
+-------+------+---+---+----+---+----+----+-------+---------------------+-------+---------------------+---+
20 rows in set (0.30 sec)
mysql> SELECT COUNT(*) FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0';
+----------+
| COUNT(*) |
+----------+
| 20 |
+----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: ref
possible_keys: idx_STDT_GR_EXMNT_NO_INF_1,idx_STDT_GR_EXMNT_NO_INF_2,idx_STDT_GR_EXMNT_NO_INF_3,idx_STDT_GR_EXMNT_NO_INF_4,idx_STDT_GR_EXMNT_NO_INF_5,idx_STDT_GR_EXMNT_NO_INF_6,idx_STDT_GR_EXMNT_NO_INF_8,idx_STDT_GR_EXMNT_NO_INF_9,idx_STDT_GR_EXMNT_NO_INF_10,idx_STDT_GR_EXMNT_NO_INF_11
key: idx_STDT_GR_EXMNT_NO_INF_6
key_len: 52
ref: const,const,const,const,const,const,const
rows: 24
Extra: Using where; Using index
1 row in set (0.00 sec)
mysql>
[26 May 2014 5:47]
MySQL Verification Team
// 5.7.5 - Not affected
mysql> select version();
+----------------------------------------------+
| version() |
+----------------------------------------------+
| 5.7.5-m15-enterprise-commercial-advanced-log |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0';
+-------+------+---+---+----+---+----+----+-------+---------------------+-------+---------------------+---+
| A | B | C | D | E | F | G | H | I | J | K | L | M |
+-------+------+---+---+----+---+----+----+-------+---------------------+-------+---------------------+---+
| 1668 | 2014 | K | 2 | 06 | 0 | 20 | 26 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1674 | 2014 | K | 2 | 06 | 0 | 20 | 28 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1680 | 2014 | K | 2 | 06 | 0 | 20 | 30 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31137 | 2014 | K | 2 | 06 | 0 | 20 | 21 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31140 | 2014 | K | 2 | 06 | 0 | 20 | 24 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31142 | 2014 | K | 2 | 06 | 0 | 20 | 26 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31144 | 2014 | K | 2 | 06 | 0 | 20 | 28 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31146 | 2014 | K | 2 | 06 | 0 | 20 | 30 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1653 | 2014 | K | 2 | 06 | 0 | 20 | 21 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1677 | 2014 | K | 2 | 06 | 0 | 20 | 29 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31138 | 2014 | K | 2 | 06 | 0 | 20 | 22 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1656 | 2014 | K | 2 | 06 | 0 | 20 | 22 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1662 | 2014 | K | 2 | 06 | 0 | 20 | 24 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31139 | 2014 | K | 2 | 06 | 0 | 20 | 23 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31141 | 2014 | K | 2 | 06 | 0 | 20 | 25 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31143 | 2014 | K | 2 | 06 | 0 | 20 | 27 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 31145 | 2014 | K | 2 | 06 | 0 | 20 | 29 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1659 | 2014 | K | 2 | 06 | 0 | 20 | 23 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1665 | 2014 | K | 2 | 06 | 0 | 20 | 25 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
| 1671 | 2014 | K | 2 | 06 | 0 | 20 | 27 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 |
+-------+------+---+---+----+---+----+----+-------+---------------------+-------+---------------------+---+
20 rows in set (0.05 sec)
mysql> SELECT COUNT(*) FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0';
+----------+
| COUNT(*) |
+----------+
| 20 |
+----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
partitions: P2014207_P2014207sp0,P2014207_P2014207sp1,P2014207_P2014207sp2,P2014207_P2014207sp3,P2014207_P2014207sp4,P2014207_P2014207sp5,P2014207_P2014207sp6,P2014207_P2014207sp7
type: ref
possible_keys: idx_STDT_GR_EXMNT_NO_INF_1,idx_STDT_GR_EXMNT_NO_INF_2,idx_STDT_GR_EXMNT_NO_INF_3,idx_STDT_GR_EXMNT_NO_INF_4,idx_STDT_GR_EXMNT_NO_INF_5,idx_STDT_GR_EXMNT_NO_INF_6,idx_STDT_GR_EXMNT_NO_INF_8,idx_STDT_GR_EXMNT_NO_INF_9,idx_STDT_GR_EXMNT_NO_INF_10,idx_STDT_GR_EXMNT_NO_INF_11
key: idx_STDT_GR_EXMNT_NO_INF_6
key_len: 52
ref: const,const,const,const,const,const,const
rows: 24
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
partitions: P2014207_P2014207sp0,P2014207_P2014207sp1,P2014207_P2014207sp2,P2014207_P2014207sp3,P2014207_P2014207sp4,P2014207_P2014207sp5,P2014207_P2014207sp6,P2014207_P2014207sp7
type: ref
possible_keys: idx_STDT_GR_EXMNT_NO_INF_1,idx_STDT_GR_EXMNT_NO_INF_2,idx_STDT_GR_EXMNT_NO_INF_3,idx_STDT_GR_EXMNT_NO_INF_4,idx_STDT_GR_EXMNT_NO_INF_5,idx_STDT_GR_EXMNT_NO_INF_6,idx_STDT_GR_EXMNT_NO_INF_8,idx_STDT_GR_EXMNT_NO_INF_9,idx_STDT_GR_EXMNT_NO_INF_10,idx_STDT_GR_EXMNT_NO_INF_11
key: idx_STDT_GR_EXMNT_NO_INF_6
key_len: 52
ref: const,const,const,const,const,const,const
rows: 24
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
Description: Seeing some wrong results from a query: Expected results: 20 Returned results: 8 I can't tell if this is a regression from another bugfix or just a new one. This behavior does not show up in MySQL 5.5.29. The examples are using partitions, but it's not related to partitions. CREATE TABLE `tab1` ( `A` char(10) NOT NULL, `B` int(11) NOT NULL, `C` char(1) NOT NULL, `D` int(11) NOT NULL, `E` char(2) NOT NULL, `F` char(5) NOT NULL, `G` char(2) NOT NULL, `H` char(4) NOT NULL, `I` varchar(15) NOT NULL, `J` datetime NOT NULL, `K` varchar(15) NOT NULL, `L` datetime NOT NULL, `M` char(1) NOT NULL, PRIMARY KEY (`A`,`B`,`C`,`D`,`E`), KEY `idx_STDT_GR_EXMNT_NO_INF_1` (`B`,`D`,`E`), KEY `idx_STDT_GR_EXMNT_NO_INF_2` (`B`,`D`,`E`,`A`), KEY `idx_STDT_GR_EXMNT_NO_INF_3` (`F`,`B`,`D`,`E`,`C`), KEY `idx_STDT_GR_EXMNT_NO_INF_4` (`F`), KEY `idx_STDT_GR_EXMNT_NO_INF_5` (`B`,`E`,`D`,`F`), KEY `idx_STDT_GR_EXMNT_NO_INF_6` (`B`,`D`,`E`,`C`,`F`,`G`,`M`), KEY `idx_STDT_GR_EXMNT_NO_INF_7` (`A`,`C`,`G`,`H`,`M`), KEY `idx_STDT_GR_EXMNT_NO_INF_8` (`B`,`D`,`E`,`M`), KEY `idx_STDT_GR_EXMNT_NO_INF_9` (`B`,`D`,`E`,`C`,`F`,`M`), KEY `idx_STDT_GR_EXMNT_NO_INF_10` (`B`,`C`,`D`,`E`,`G`,`F`,`M`), KEY `idx_STDT_GR_EXMNT_NO_INF_11` (`B`,`C`,`D`,`E`,`F`,`G`,`M`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 /*!50500 PARTITION BY RANGE COLUMNS(B,D,E) SUBPARTITION BY KEY (A) (PARTITION P2013104 VALUES LESS THAN (2013,1,'04') (SUBPARTITION P2013104sp0 ENGINE = InnoDB, <snip, since bug reports have an 8K limit> SUBPARTITION P2016207sp7 ENGINE = InnoDB)) */ mysql> SELECT VERSION(); +------------+ | VERSION() | +------------+ | 5.5.37-log | +------------+ 1 row in set (0.03 sec) mysql> SELECT * FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0'; +-------+------+---+---+----+---+----+----+-------+---------------------+-------+---------------------+---+ | A | B | C | D | E | F | G | H | I | J | K | L | M | +-------+------+---+---+----+---+----+----+-------+---------------------+-------+---------------------+---+ | 1668 | 2014 | K | 2 | 06 | 0 | 20 | 26 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 | | 1674 | 2014 | K | 2 | 06 | 0 | 20 | 28 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 | | 1680 | 2014 | K | 2 | 06 | 0 | 20 | 30 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 | | 31137 | 2014 | K | 2 | 06 | 0 | 20 | 21 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 | | 31140 | 2014 | K | 2 | 06 | 0 | 20 | 24 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 | | 31142 | 2014 | K | 2 | 06 | 0 | 20 | 26 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 | | 31144 | 2014 | K | 2 | 06 | 0 | 20 | 28 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 | | 31146 | 2014 | K | 2 | 06 | 0 | 20 | 30 | DUMMY | 2014-05-23 00:00:00 | DUMMY | 2014-05-23 00:00:00 | 0 | +-------+------+---+---+----+---+----+----+-------+---------------------+-------+---------------------+---+ 8 rows in set (1.03 sec) mysql> SELECT COUNT(*) FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0'; +----------+ | COUNT(*) | +----------+ | 20 | +----------+ 1 row in set (0.01 sec) mysql> EXPLAIN SELECT COUNT(*) FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tab1 type: ref possible_keys: idx_STDT_GR_EXMNT_NO_INF_1,idx_STDT_GR_EXMNT_NO_INF_2,idx_STDT_GR_EXMNT_NO_INF_3,idx_STDT_GR_EXMNT_NO_INF_4,idx_STDT_GR_EXMNT_NO_INF_5,idx_STDT_GR_EXMNT_NO_INF_6,idx_STDT_GR_EXMNT_NO_INF_8,idx_STDT_GR_EXMNT_NO_INF_9,idx_STDT_GR_EXMNT_NO_INF_10,idx_STDT_GR_EXMNT_NO_INF_11 key: idx_STDT_GR_EXMNT_NO_INF_11 key_len: 52 ref: const,const,const,const,const,const,const rows: 24 Extra: Using where; Using index 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM tab1 WHERE B=2014 AND D=2 AND E='06' AND C = 'K' AND F = '0' AND G = '20' AND M = '0'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tab1 type: index_merge possible_keys: idx_STDT_GR_EXMNT_NO_INF_1,idx_STDT_GR_EXMNT_NO_INF_2,idx_STDT_GR_EXMNT_NO_INF_3,idx_STDT_GR_EXMNT_NO_INF_4,idx_STDT_GR_EXMNT_NO_INF_5,idx_STDT_GR_EXMNT_NO_INF_6,idx_STDT_GR_EXMNT_NO_INF_8,idx_STDT_GR_EXMNT_NO_INF_9,idx_STDT_GR_EXMNT_NO_INF_10,idx_STDT_GR_EXMNT_NO_INF_11 key: idx_STDT_GR_EXMNT_NO_INF_11,idx_STDT_GR_EXMNT_NO_INF_10,idx_STDT_GR_EXMNT_NO_INF_6,idx_STDT_GR_EXMNT_NO_INF_5 key_len: 52,52,52,36 ref: NULL rows: 3 Extra: Using intersect(idx_STDT_GR_EXMNT_NO_INF_11,idx_STDT_GR_EXMNT_NO_INF_10,idx_STDT_GR_EXMNT_NO_INF_6,idx_STDT_GR_EXMNT_NO_INF_5); Using where 1 row in set (0.01 sec) mysql> How to repeat: Test schema attached.