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:
None 
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
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.
[26 May 2014 4:28] Will Fong
Schema

Attachment: TAB1.sql.bz2 (application/x-bzip, text), 208.87 KiB.

[26 May 2014 5:37] Umesh Shastry
Hello Will,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh
[26 May 2014 5:45] Umesh Shastry
// 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] Umesh Shastry
// 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] Umesh Shastry
// 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)