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
}
~~~
© 2024, Oracle Corporation and/or its affiliates
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 } ~~~