How to make slow MySQL queries run faster-PHP Chinese Network Q&A

Whenever I run this query, it keeps showing "Error code 2013. Lost connection to mysql query":

DROP TABLE IF EXISTS elogbook_get_boardid; CREATE TABLE elogbook_get_boardid AS (SELECT DISTINCT `LOTID`, `Board_ID`, `Serial_Number`, coalesce(CASE WHEN A.`Serial_Number` = B.`board_sn` THEN 'In Use' ELSE A.`status` END, '') AS `Status`, coalesce(B.`LOT_LOCATION`, '') AS `chamber`, coalesce(B.`created_date`, '') AS `Start Date`, coalesce(B.`BINOUT_DUE_DATE`, '') AS `Est End` FROM hardware_tracking_msa.HAST_Detail A LEFT JOIN (SELECT X.*, Y.`BINOUT_DUE_DATE`, Y.`LOT_LOCATION` FROM skynet_msa.lots_to_hast_boards X LEFT JOIN skynet_msa.labs_inventory Y ON X.`lotid` = Y.`LOTID`) B ON A.`Serial_Number` = B.`board_sn` WHERE `LOTID` IS NOT NULL);

I would like to know what is causing the running time to exceed 30 seconds and how to improve the query. Any help is appreciated, thank you!

Output of hardware_tracking_msa.HAST_Detail:

Index, Board_Number, SIG_Number, Board_ID, Serial_Number, Design_ID, Package, Sockets, Socket_Number, Status, Notes, Deleted_By, Inserted_Date, Inserted_By, Updated_Date, Updated_By, Deleted_Date '1', '2759', '594-11269', '2759-001', '605637/001', 'J4Q2', 'WFBGA, PIN COUNT: 168/529,PACKAGE SIZE: 12x12', '40', '530-10670 SINGLE BIAS (ESPEC)CANNOT USE FOR J80C/J4Q2', '', '', '', '2022-05-17 14:00:00', 'NWANGA', '2022-05-18 10:15:41', 'tmingyao', '2022-05-17 14:57:33' '2', '2759', '594-11269', '2759-002', '605637/008', 'J4Q2', 'WFBGA, PIN COUNT: 168/529,PACKAGE SIZE: 12x12', '40', '530-10670 SINGLE BIAS (ESPEC)CANNOT USE FOR J80C/J4Q2', NULL, '', '', '2022-05-17 14:00:00', 'NWANGA', '2022-05-17 14:57:33', '', '2022-05-17 14:57:33' '3', '2759', '594-11269', '2759-003', '608061/001', 'J4Q2', 'WFBGA, PIN COUNT: 168/529,PACKAGE SIZE: 12x12', '40', '530-10670 SINGLE BIAS (ESPEC)CANNOT USE FOR J80C/J4Q2', NULL, '', '', '2022-05-17 14:00:00', 'NWANGA', '2022-05-17 14:57:33', '', '2022-05-17 14:57:33'

Output of skynet_msa.lots_to_hast_boards:

\begin{table}[] \begin{tabular}{lll} lotid, & board\_sn, & created\_date \ 'CVZ2JL2.11', & '1790247/003', & '2022-07-20 '14:26:04 \ 'CV4YJL2.11', & '1317876/002', & '2022-07-20 14:26:04 \ 'CVRMHL2.11', & '1790241/014', & '2022-07-20 14:26:04 \end{tabular} \end{table}

Output of skynet_msa.labs_inventory:

LOTID, LOCATION, ENV_TEST_INTERVAL, EST_DURATION_TIME, ENV_STRESS_VOLTAGE, ENV_STRESS_VOLT_2, ENV_STRESS_VOLT_3, PRODUCT_FAMILY, PRODUCT_TECHNOLOGY, DESIGN_ID, QA_WORK_REQUEST_NO, QA_PROCESS_TYPE, QA_PROCESS_NAME, QA_BURN_EXPERIMENT, QA_CONTACT, QA_PROCESS_LOT_NO, FABRICATION_FACILITY, ASSEMBLY_FACILITY, ELEC_TEST_FLOW, CONFIGURATION_WIDTH, NUMBER_OF_DIE_IN_PKG, CURRENT_QTY, LOT_LOCATION, LEAD_COUNT, PACKAGE_TYPE, PACKAGE_LENGTH, PACKAGE_WIDTH, PACKAGE_HEIGHT, SOAK_LEVEL, BAKE_TEMPERATURE, DRB_TEMPERATURE, ACTUAL_CURE_TIME, REFLOW_PROFILE, PINOUT_VERSION, DISPATCH_DUE_DATE, BINOUT_DUE_DATE, ROW_CREATED, ROW_MODIFIED, LOCATION_DATE, LOCATION_WW, MODULE_LOT, BURN_LOT, MONITOR_IGNORE, TICKER, PRIORITY, ASM_LOT_NUMBER, MARK_FORMAT, LOCATION_TAT, RPM_WW, QA_EVENT_ID, TC_WEIGHT, AUTOMOTIVE_LOT, CUSTOMER_OPTION, PKG_RECEIVE_DATE, CUSTOMER_GROUP, SAMPLE_PULLED_DATE, QA_SPECIAL_FLOW, QA_BLOCKS, PROBE_CUSTOM_TESTED, QA_PROGRAM_REV, NAND_FLOW_TYPE, NUM_FLASH_CE_PINS, RETICLE_WAVE_ID, MAJOR_PROBE_PROG_REV, MAJOR_TEST_PROG_REV, CYCLING_TYPE, QA_TARGET_CYCLE, LAST_TEST_INTERVAL, CYCLING_TEMPERATURE, ENV_STRESS_DURATION, FIRST_TEST_INTERVAL, DRB_TARGET_INTERVAL, LTDR_TEMPERATURE, RD_STRESS_TYPE '1623941.001', 'TEMP CYCLE K INV', '25', '12.50', '0.000', '0.000', '0.000', 'MCP', 'ALL IN ONE MCP', 'UM181', '', 'PRODUCTION SCREEN', 'TEMP CYCLE K', '', '', '', 'MIXED', 'ASSEMBLY-MSA', '', '', '8', '2439', 'S01-AR-ASRSIN', '254/432', 'TFBGA', '13.000', '11.500', '1.100', '', '0', '0', '0', '', 'JEDEC', '1970-01-01 00:00:00', NULL, '2022-06-05 18:35:12', '2022-06-06 00:00:16', '2022-06-05 18:35:00', '202223', '0', '0', '0', '0', '4', '1623941.001', '', '1.57', '', 'QA 13', '0', '', '', '1970-01-01 00:00:00', '', '1970-01-01 00:00:00', '', '', 'MOBILE C', '', '', '4', '', '', '', '', '', '', '', '25', '', '', '', '' 'BC4WSXZ.31', 'THERMAL WARPAGE', '0', '0.00', '0.000', '0.000', '0.000', 'MCP', 'MASSFLASH/LPDDR4', 'J86L', '', 'PRODUCTION SCREEN', 'THERMAL WARPAGE', '', '', '', 'MIXED', 'PTI P3', '', '', '2', '26', '', '194/1026', 'UFBGA', '9.000', '12.500', '0.545', '', '0', '0', '0', '', 'AVALON', '1970-01-01 00:00:00', NULL, '2022-07-19 11:00:17', '2022-07-20 13:30:15', '2022-07-19 11:26:45', '202229', '0', '0', '0', '0', '4', 'PT22900.25', 'AVALON', '0.28', '', 'QA 32', '0', '', '', '1970-01-01 00:00:00', '', '2022-07-19 10:58:00', '', '', '', '', '', '1', '', '', '29', '', '', '', '', '', '', '', '', '' 'BC6VVLZ.31', 'TEMP CYCLE K INV', '25', '12.50', '0.000', '0.000', '0.000', 'MCP', 'MASSFLASH/CONTROLLER', 'J39E', '', 'PRODUCTION SCREEN', 'TEMP CYCLE K', '', '', '', 'FAB 10', 'ASSEMBLY-MSA', '', 'X4-X8', '4', '320', 'S01-REL-LAB-IN', '153/196', 'VFBGA', '13.000', '11.500', '1.000', '', '0', '0', '0', '', 'JEDEC', '1970-01-01 00:00:00', NULL, '2022-07-10 14:35:16', '2022-07-11 07:15:19', '2022-07-10 14:31:49', '202228', '0', '0', '0', '0', '4', 'BF3HFCQ.5X', '', '419.25', '', 'QA 45', '0', 'YES', 'AUTOMOTIVE', '1970-01-01 00:00:00', '', '1970-01-01 00:00:00', '', '', 'NAND AUTO', '', '', '4', 'WAVE007', '22', '', '', '', '', '', '25', '', '', '', ''

Display the created table hardware_tracking_msa.HAST_Detail:

CREATE TABLE `HAST_Detail` ( `Index` int NOT NULL AUTO_INCREMENT, `Board_Number` varchar(250) DEFAULT \'\', `SIG_Number` varchar(250) DEFAULT \'\', `Board_ID` varchar(250) DEFAULT \'\', `Serial_Number` varchar(250) DEFAULT \'\', `Design_ID` varchar(150) DEFAULT \'\', `Package` varchar(250) DEFAULT \'\', `Sockets` int DEFAULT \'0\', `Socket_Number` varchar(250) DEFAULT \'\', `Status` varchar(45) DEFAULT NULL, `Notes` varchar(1000) DEFAULT \'\', `Deleted_By` varchar(20) DEFAULT \'\', `Inserted_Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `Inserted_By` varchar(20) NOT NULL DEFAULT \'\', `Updated_Date` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `Updated_By` varchar(20) DEFAULT \'\', `Deleted_Date` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`Index`) ) ENGINE=InnoDB AUTO_INCREMENT=1459 DEFAULT CHARSET=utf8'

Display the created table skynet_msa.lots_to_hast_boards:

CREATE TABLE `lots_to_hast_boards` ( `lotid` varchar(45) NOT NULL DEFAULT \'\', `board_sn` varchar(45) NOT NULL DEFAULT \'\', `created_date` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`lotid`,`board_sn`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1'

Display the created table skynet_msa.labs_inventory:

CREATE TABLE `labs_inventory` ( `LOTID` varchar(12) NOT NULL, `LOCATION` varchar(48) NOT NULL, `ENV_TEST_INTERVAL` int DEFAULT \'0\', `EST_DURATION_TIME` decimal(8,2) DEFAULT \'0.00\', `ENV_STRESS_VOLTAGE` decimal(6,3) DEFAULT NULL, `ENV_STRESS_VOLT_2` decimal(6,3) DEFAULT NULL, `ENV_STRESS_VOLT_3` decimal(6,3) DEFAULT NULL, `PRODUCT_FAMILY` varchar(45) DEFAULT NULL, `PRODUCT_TECHNOLOGY` varchar(45) DEFAULT NULL, `DESIGN_ID` varchar(6) DEFAULT NULL, `QA_WORK_REQUEST_NO` varchar(100) DEFAULT NULL, `QA_PROCESS_TYPE` varchar(45) DEFAULT NULL, `QA_PROCESS_NAME` varchar(64) DEFAULT NULL, `QA_BURN_EXPERIMENT` varchar(45) DEFAULT NULL, `QA_CONTACT` varchar(45) DEFAULT NULL, `QA_PROCESS_LOT_NO` varchar(12) DEFAULT NULL, `FABRICATION_FACILITY` varchar(45) DEFAULT NULL, `ASSEMBLY_FACILITY` varchar(45) DEFAULT NULL, `ELEC_TEST_FLOW` varchar(45) DEFAULT NULL, `CONFIGURATION_WIDTH` varchar(8) DEFAULT NULL, `NUMBER_OF_DIE_IN_PKG` int DEFAULT NULL, `CURRENT_QTY` int DEFAULT NULL, `LOT_LOCATION` varchar(45) DEFAULT NULL, `LEAD_COUNT` varchar(45) DEFAULT \'\', `PACKAGE_TYPE` varchar(45) DEFAULT \'\', `PACKAGE_LENGTH` decimal(6,3) DEFAULT \'0.000\', `PACKAGE_WIDTH` decimal(6,3) DEFAULT \'0.000\', `PACKAGE_HEIGHT` decimal(6,3) DEFAULT \'0.000\', `SOAK_LEVEL` varchar(45) DEFAULT NULL, `BAKE_TEMPERATURE` varchar(30) DEFAULT NULL, `DRB_TEMPERATURE` varchar(30) DEFAULT NULL, `ACTUAL_CURE_TIME` int DEFAULT NULL, `REFLOW_PROFILE` varchar(45) DEFAULT NULL, `PINOUT_VERSION` varchar(45) DEFAULT NULL, `DISPATCH_DUE_DATE` datetime DEFAULT NULL, `BINOUT_DUE_DATE` datetime DEFAULT NULL, `ROW_CREATED` datetime NOT NULL, `ROW_MODIFIED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `LOCATION_DATE` timestamp NOT NULL DEFAULT \'1970-01-01 12:00:00\', `LOCATION_WW` varchar(10) DEFAULT NULL, `MODULE_LOT` int NOT NULL DEFAULT \'0\', `BURN_LOT` int DEFAULT \'0\', `MONITOR_IGNORE` int NOT NULL DEFAULT \'0\', `TICKER` int NOT NULL DEFAULT \'0\', `PRIORITY` varchar(4) DEFAULT NULL, `ASM_LOT_NUMBER` varchar(45) DEFAULT NULL, `MARK_FORMAT` varchar(45) DEFAULT NULL, `LOCATION_TAT` double DEFAULT \'0\', `RPM_WW` varchar(10) DEFAULT NULL, `QA_EVENT_ID` varchar(15) DEFAULT NULL, `TC_WEIGHT` double DEFAULT \'0\', `AUTOMOTIVE_LOT` varchar(45) DEFAULT NULL, `CUSTOMER_OPTION` varchar(45) DEFAULT NULL, `PKG_RECEIVE_DATE` datetime DEFAULT NULL, `CUSTOMER_GROUP` varchar(45) DEFAULT NULL, `SAMPLE_PULLED_DATE` datetime DEFAULT NULL, `QA_SPECIAL_FLOW` varchar(45) DEFAULT NULL, `QA_BLOCKS` varchar(45) DEFAULT NULL, `PROBE_CUSTOM_TESTED` varchar(45) DEFAULT NULL, `QA_PROGRAM_REV` varchar(45) DEFAULT NULL, `NAND_FLOW_TYPE` varchar(45) DEFAULT NULL, `NUM_FLASH_CE_PINS` varchar(45) DEFAULT NULL, `RETICLE_WAVE_ID` varchar(45) DEFAULT NULL, `MAJOR_PROBE_PROG_REV` varchar(45) DEFAULT NULL, `MAJOR_TEST_PROG_REV` varchar(45) DEFAULT NULL, `CYCLING_TYPE` varchar(45) DEFAULT NULL, `QA_TARGET_CYCLE` varchar(45) DEFAULT NULL, `LAST_TEST_INTERVAL` varchar(45) DEFAULT NULL, `CYCLING_TEMPERATURE` varchar(45) DEFAULT NULL, `ENV_STRESS_DURATION` varchar(45) DEFAULT NULL, `FIRST_TEST_INTERVAL` varchar(45) DEFAULT NULL, `DRB_TARGET_INTERVAL` varchar(45) DEFAULT NULL, `LTDR_TEMPERATURE` varchar(45) DEFAULT NULL, `RD_STRESS_TYPE` varchar(45) DEFAULT NULL, PRIMARY KEY (`LOTID`), KEY `design_id` (`DESIGN_ID`), KEY `lot_location` (`LOT_LOCATION`), KEY `burn` (`DESIGN_ID`,`QA_BURN_EXPERIMENT`), KEY `locations` (`LOT_LOCATION`,`LOCATION`), KEY `all_index` (`LOCATION`,`LOT_LOCATION`,`DISPATCH_DUE_DATE`,`PRODUCT_FAMILY`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=\'table to store MAM data for msa labs skynet\''

Explain query:

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra '1', 'SIMPLE', 'X', NULL, 'ALL', 'PRIMARY', NULL, NULL, NULL, '55', '90.00', 'Using where' '1', 'SIMPLE', 'Y', NULL, 'eq_ref', 'PRIMARY', 'PRIMARY', '14', 'skynet_msa.X.lotid', '1', '100.00', 'Using where' '1', 'SIMPLE', 'A', NULL, 'ALL', NULL, NULL, NULL, NULL, '1458', '10.00', 'Using where; Using join buffer (hash join)'

reply all (1)

P粉8313104042024-04-01 00:03:07 1 floor

Change from MyISAM to InnoDB. (This may not be important forthisquery.)

Tentative addition of index:

A: INDEX(Serial_Number, status)

LEFT not required:

LEFT JOIN ( ... ) B ON ... WHERE b.id IS NOT NULL

-->

JOIN ( ... ) B ON ...

At this point, it maybe reasonable to get rid of the nested LEFT JOIN and simply join the single levels A,X,Y together.

The explanation suggests that at least some of these simplifications are figured out automatically by the optimizer.

A.Serial_Number= B.`board_sn

I see "utf8" and "latin1". If any query JOINs a VARCHAR between different character sets (or collations), no other suitable index will be used. If this is a problem, I suggest you useALTER .. CONVERT TO ..to change latin1 to utf8.

I see columns named "DURATION" and "INTERVAL" declared asVARCHAR. This can cause trouble if you do number crunching on such columns. (I know things like "BOARD_SN" aren't really numbers.)