Re: No Query result with more then 4000 records

[Martin Roehler]

The Query:
********************************
INSERT INTO `dai_csb_test`.`allfin_result`
(
-- `idallfin_result_ID`,
`ALLFIN_PASS166_ID`,
`FIN`,
`TTZ`,
`BR_E`,
`AA`,
`DIOGENESNAME`,
`SOFTWARE_SNR`,
`SCN`,
`WERKS_SCN`,
`TEIL_TNR`,
`TEIL_VOR_TNR`,
`SDB`)
(
SELECT
allfin_pass166.ALLFIN_PASS166_ID,
allfin_pass166.FIN,
allfin_pass166.TTZ,
T10RX2E.X2E_BR_E,
T10RX2E.X2E_AA,
allfin_pass166.DIOGENESNAME,
allfin_pass166.SOFTWARE_SNR,
allfin_pass166.SCN,
allfin_pass166.WERKS_SCN,
T10RTAAK.TAAK_TEIL_TNR,
T10RTAAK.TAAK_TEIL_VOR_TNR,
T10RTAAK.TAAK_SDB
FROM
allfin_pass166
INNER JOIN T10RX2E ON
(mid(T10RX2E.X2E_BMAA_E,2,7) = mid(allfin_pass166.FIN,4,7)
AND (substr(T10RX2E.X2E_SDA,1,8) <= REPLACE(TTZ, '-', ''))
AND (substr(T10RX2E.X2E_SDB,1,8) > REPLACE(TTZ, '-', ''))
)
INNER JOIN T10RTAAK ON
(CONCAT('A', SOFTWARE_SNR) = MID(TAAK_TEIL_TNR,1,11)
AND (T10RX2E.X2E_AA = T10RTAAK.TAAK_AA)
AND (T10RX2E.X2E_BR_E = T10RTAAK.TAAK_BR))

where (ALLFIN_PASS166_ID > 0 AND ALLFIN_PASS166_ID <= 5000 ) -- << Here you can restrict the result
)
;

*******************************
The Create Table:
*******************************
CREATE TABLE `allfin_pass166` (
`ALLFIN_PASS166_ID` bigint(30) NOT NULL AUTO_INCREMENT,
`FIN` varchar(17) NOT NULL,
`WERK` varchar(15) DEFAULT NULL,
`TTZ` varchar(15) DEFAULT NULL,
`DIOGENESNAME` varchar(45) DEFAULT NULL,
`HARDWARE_SNR` varchar(45) DEFAULT NULL,
`SOFTWARE_SNR` varchar(45) DEFAULT NULL,
`SCN` varchar(45) DEFAULT NULL,
`WERKS_SCN` varchar(45) DEFAULT NULL,
PRIMARY KEY (`ALLFIN_PASS166_ID`),
UNIQUE KEY `ALLFIN_PASS166_ID_UNIQUE` (`ALLFIN_PASS166_ID`),
KEY `ALLFIN_PASS166_SW_KEY` (`SOFTWARE_SNR`,`FIN`),
KEY `idx_allfin_pass166_FIN_TTZ` (`FIN`,`TTZ`)
) ENGINE=InnoDB AUTO_INCREMENT=7742881 DEFAULT CHARSET=utf8;
-- size 7742880 records
------------------------------
CREATE TABLE `t10rtaak` (
`TAAK_TEIL` varchar(45) NOT NULL,
`TAAK_TEIL_VOR` varchar(45) NOT NULL,
`TAAK_SATZ` varchar(15) NOT NULL,
`TAAK_SDA` varchar(15) NOT NULL,
`TAAK_SDB` varchar(15) DEFAULT NULL,
`TAAK_BR` varchar(45) DEFAULT NULL,
`TAAK_AA` varchar(45) DEFAULT NULL,
`TAAK_L` varchar(45) DEFAULT NULL,
`TAAK_CR` varchar(150) DEFAULT NULL,
`TAAK_DN` varchar(128) DEFAULT NULL,
`TAAK_AUS` varchar(45) DEFAULT NULL,
`TAAK_TEIL_TNR` varchar(45) DEFAULT NULL,
`TAAK_TEIL_ES1` varchar(45) DEFAULT NULL,
`TAAK_TEIL_ES2` varchar(45) DEFAULT NULL,
`TAAK_TEIL_D` varchar(45) DEFAULT NULL,
`TAAK_TEIL_VOR_TNR` varchar(45) DEFAULT NULL,
`TAAK_TEIL_VOR_ES1` varchar(45) DEFAULT NULL,
`TAAK_TEIL_VOR_ES2` varchar(45) DEFAULT NULL,
`TAAK_TEIL_VOR_D` varchar(45) DEFAULT NULL,
PRIMARY KEY (`TAAK_TEIL`,`TAAK_TEIL_VOR`,`TAAK_SATZ`,`TAAK_SDA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- size 63375 records
--------------------------------------
CREATE TABLE `t10rx2e` (
`X2E_ID` bigint(15) NOT NULL AUTO_INCREMENT,
`X2E_BR` varchar(45) DEFAULT NULL,
`X2E_AA` varchar(15) DEFAULT NULL,
`X2E_BMAA` varchar(45) DEFAULT NULL,
`X2E_SDA` varchar(15) DEFAULT NULL,
`X2E_SDB` varchar(15) DEFAULT NULL,
`X2E_LK` varchar(15) DEFAULT NULL,
`X2E_PGKZ` varchar(15) DEFAULT NULL,
`X2E_CBED` varchar(500) DEFAULT NULL,
`X2E_DUMMY` varchar(15) DEFAULT NULL,
`X2E_BR_E` varchar(45) DEFAULT NULL,
`X2E_BMAA_E` varchar(45) DEFAULT NULL,
PRIMARY KEY (`X2E_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=15929 DEFAULT CHARSET=utf8;
-- size 15928 records
****************************
The Explain
****************************
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "37516089421.22"
},
"nested_loop": [
{
"table": {
"table_name": "T10RX2E",
"access_type": "ALL",
"rows_examined_per_scan": 15989,
"rows_produced_per_join": 15989,
"filtered": "100.00",
"cost_info": {
"read_cost": "225.00",
"eval_cost": "3197.80",
"prefix_cost": "3422.80",
"data_read_per_join": "35M"
},
"used_columns": [
"X2E_AA",
"X2E_SDA",
"X2E_SDB",
"X2E_BR_E",
"X2E_BMAA_E"
]
}
},
{
"table": {
"table_name": "T10RTAAK",
"access_type": "ALL",
"rows_examined_per_scan": 61208,
"rows_produced_per_join": 9786547,
"filtered": "1.00",
"using_join_buffer": "Block Nested Loop",
"cost_info": {
"read_cost": "58032.94",
"eval_cost": "1957309.56",
"prefix_cost": "195792398.14",
"data_read_per_join": "26G"
},
"used_columns": [
"TAAK_SDB",
"TAAK_BR",
"TAAK_AA",
"TAAK_TEIL_TNR",
"TAAK_TEIL_VOR_TNR"
],
"attached_condition": "((`dai_csb_test`.`t10rtaak`.`TAAK_BR` = `dai_csb_test`.`t10rx2e`.`X2E_BR_E`) and (`dai_csb_test`.`t10rx2e`.`X2E_AA` = `dai_csb_test`.`t10rtaak`.`TAAK_AA`))"
}
},
{
"table": {
"table_name": "allfin_pass166",
"access_type": "range",
"possible_keys": [
"PRIMARY",
"ALLFIN_PASS166_ID_UNIQUE"
],
"key": "PRIMARY",
"used_key_parts": [
"ALLFIN_PASS166_ID"
],
"key_length": "8",
"rows_examined_per_scan": 9502,
"rows_produced_per_join": 92991777316,
"filtered": "100.00",
"using_join_buffer": "Block Nested Loop",
"cost_info": {
"read_cost": "18721941559.83",
"eval_cost": "18598355463.25",
"prefix_cost": "37516089421.22",
"data_read_per_join": "71T"
},
"used_columns": [
"ALLFIN_PASS166_ID",
"FIN",
"TTZ",
"DIOGENESNAME",
"SOFTWARE_SNR",
"SCN",
"WERKS_SCN"
],
"attached_condition": "((`dai_csb_test`.`allfin_pass166`.`ALLFIN_PASS166_ID` > 0) and (`dai_csb_test`.`allfin_pass166`.`ALLFIN_PASS166_ID` <= 5000) and (concat('A',`dai_csb_test`.`allfin_pass166`.`SOFTWARE_SNR`) = substr(`dai_csb_test`.`t10rtaak`.`TAAK_TEIL_TNR`,1,11)) and (substr(`dai_csb_test`.`t10rx2e`.`X2E_BMAA_E`,2,7) = substr(`dai_csb_test`.`allfin_pass166`.`FIN`,4,7)) and (substr(`dai_csb_test`.`t10rx2e`.`X2E_SDA`,1,8) <= replace(`dai_csb_test`.`allfin_pass166`.`TTZ`,'-','')) and (substr(`dai_csb_test`.`t10rx2e`.`X2E_SDB`,1,8) > replace(`dai_csb_test`.`allfin_pass166`.`TTZ`,'-','')))"
}
}
]
}
}

how can i send the Explain as Picture?
regards
MArtin