Bug #91589 Invalid primary key improves query performance
Submitted: 10 Jul 2018 9:44 Modified: 10 Jul 2018 9:51
Reporter: 国强 徐 Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.33-79.0 OS:CentOS (6.3)
Assigned to: CPU Architecture:x86

[10 Jul 2018 9:44] 国强 徐
Description:
The query plan changes when add logically invalid primary key column condition.

Add this primary condition make the execution more efficient.

How to repeat:
CREATE TABLE `account_detail` (
  `F_auto_id` bigint(32) unsigned NOT NULL AUTO_INCREMENT,
  `TXN_SEQ_NO` bigint(32) NOT NULL,
  `SYS_TRACE_NO` varchar(32) DEFAULT NULL,
  `ACCOUNTING_DATE` varchar(8) DEFAULT NULL,
  `TXN_TIME` datetime NOT NULL,
  `ACCOUNT_NO` varchar(32) DEFAULT NULL,
  `TXN_TYPE` int(1) DEFAULT NULL,
  `TXN_DSCPT` varchar(256) DEFAULT NULL,
  `CHANGE_TYPE` int(1) DEFAULT NULL,
  `DIRECTION` varchar(1) DEFAULT NULL,
  `TXN_AMT` decimal(19,4) DEFAULT NULL,
  `BEFORE_AMT` decimal(19,4) DEFAULT NULL,
  `AFTER_AMT` decimal(19,4) DEFAULT NULL,
  `ENTRY_SEQ_NO` varchar(32) DEFAULT NULL,
  `OTHER_ACCOUNT_NO` varchar(32) DEFAULT NULL,
  `OLD_TXN_SEQ_NO` varchar(32) DEFAULT NULL,
  `REMARK` varchar(256) DEFAULT NULL,
  `CRDR` int(1) DEFAULT NULL,
  `PRODUCT_CODE` varchar(12) DEFAULT NULL,
  `PAY_CODE` varchar(12) DEFAULT NULL,
  `OPERATION_TYPE` int(1) DEFAULT NULL,
  `DELETE_SIGN` int(1) DEFAULT NULL,
  `SUITE_NO` varchar(32) DEFAULT NULL,
  `CONTEXT_VOUCHER_NO` varchar(32) DEFAULT NULL,
  `TRANSACTION_NO` varchar(32) DEFAULT NULL,
  `VOUCHER_NO` varchar(50) DEFAULT NULL,
  `STATUS` int(1) DEFAULT '12',
  `UPDATE_TIME` datetime DEFAULT NULL,
  PRIMARY KEY (`F_auto_id`),
  UNIQUE KEY `UIDX_IAD_VO` (`VOUCHER_NO`),
  KEY `AK_INNER_KEY_ACCOUNT_NO` (`ACCOUNT_NO`),
  KEY `IDX_DIAD_STN` (`SYS_TRACE_NO`),
  KEY `IDX_ACCOUNTING_DATE` (`ACCOUNTING_DATE`)
) ENGINE=InnoDB AUTO_INCREMENT=1329833305 DEFAULT CHARSET=utf8;

Query one:

> explain SELECT f_auto_id,txn_seq_no,sys_trace_no,accounting_date,txn_time,account_no,txn_type,txn_dscpt,change_type,direction,txn_amt,before_amt,after_amt,entry_seq_no,other_account_no,old_txn_seq_no,remark,crdr,product_code,pay_code,operation_type,delete_sign,suite_no,context_voucher_no,transaction_no,voucher_no,status,update_time FROM account_detail WHERE accounting_date < '20180311' AND accounting_date >= '20180310' and voucher_no > '20180310001111000066210778054009' order by voucher_no LIMIT 0, 3000;
+----+-------------+----------------------------+-------+---------------------------------+-------------+---------+------+-----------+------------------------------------+
| id | select_type | table                      | type  | possible_keys                   | key         | key_len | ref  | rows      | Extra                              |
+----+-------------+----------------------------+-------+---------------------------------+-------------+---------+------+-----------+------------------------------------+
|  1 | SIMPLE      | account_detail | range | UIDX_IAD_VO,IDX_ACCOUNTING_DATE | UIDX_IAD_VO | 153     | NULL | 162225955 | Using index condition; Using where |
+----+-------------+----------------------------+-------+---------------------------------+-------------+---------+------+-----------+------------------------------------+
1 row in set (0.00 sec)

> optimizer_trace
~~~
"analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "UIDX_IAD_VO",
                        "ranges": [
                          "20180310001111000066210778054009 < VOUCHER_NO"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 162249628,
                        "cost": 1.95e8,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "IDX_ACCOUNTING_DATE",
                        "ranges": [
                          "20180310 <= ACCOUNTING_DATE < 20180311"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 7982862,
                        "cost": 9.58e6,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "IDX_ACCOUNTING_DATE",
                      "rows": 7982862,
                      "ranges": [
                        "20180310 <= ACCOUNTING_DATE < 20180311"
                      ]
                    },
                    "rows_for_plan": 7982862,
                    "cost_for_plan": 9.58e6,
                    "chosen": true
                  }
~~~	

Query two:

Added least primary key value of query one result.

> explain SELECT f_auto_id,txn_seq_no,sys_trace_no,accounting_date,txn_time,account_no,txn_type,txn_dscpt,change_type,direction,txn_amt,before_amt,after_amt,entry_seq_no,other_account_no,old_txn_seq_no,remark,crdr,product_code,pay_code,operation_type,delete_sign,suite_no,context_voucher_no,transaction_no,voucher_no,status,update_time FROM account_detail WHERE f_auto_id>=1128351862 and accounting_date < '20180311' AND accounting_date >= '20180310' and voucher_no > '20180310001111000066210778054009' order by voucher_no LIMIT 0, 3000;
+----+-------------+----------------------------+-------+-----------------------------------------+---------------------+---------+------+------+----------------------------------------------------+
| id | select_type | table                      | type  | possible_keys                           | key                 | key_len | ref  | rows | Extra                                              |
+----+-------------+----------------------------+-------+-----------------------------------------+---------------------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | account_detail | range | PRIMARY,UIDX_IAD_VO,IDX_ACCOUNTING_DATE | IDX_ACCOUNTING_DATE | 35      | NULL | 1975 | Using index condition; Using where; Using filesort |
+----+-------------+----------------------------+-------+-----------------------------------------+---------------------+---------+------+------+----------------------------------------------------+
1 row in set (0.00 sec)

> optimizer_trace
~~~
"analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "1128351862 <= F_auto_id"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 162303060,
                        "cost": 3.31e7,
                        "chosen": true
                      },
                      {
                        "index": "UIDX_IAD_VO",
                        "ranges": [
                          "20180310001111000066210778054009 < VOUCHER_NO"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 162303060,
                        "cost": 1.95e8,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "IDX_ACCOUNTING_DATE",
                        "ranges": [
                          "20180310 <= ACCOUNTING_DATE < 20180311"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 1975,
                        "cost": 2371,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "IDX_ACCOUNTING_DATE",
                      "rows": 1975,
                      "ranges": [
                        "20180310 <= ACCOUNTING_DATE < 20180311"
                      ]
                    },
                    "rows_for_plan": 1975,
                    "cost_for_plan": 2371,
                    "chosen": true
                  }
~~~