I have a few problems with MYSQL database that I can't solve.
My query below is taking too much time and making the system hang. I'm trying to use the "JOIN" construct to develop this. But this time my aggregation, which I'm trying to do with "SUM", reduces the query to one line. Is it ok to do this job with "JOIN"? or how should i improve this query.
This database works with a total of 22 client devices in ASP .NET application. As I mentioned above, in cases where the query time is long, when the client devices send a query to the database at the same time, the client device freezes. What I don't understand is why a query in the browser app is making all devices wait. Isn't each query processed as a separate "Thread" in MYSQL? So if a query has a return time of 10 seconds, will all clients wait 10 seconds for the query to be answered in the browser?
SELECT *,
(SELECT MachModel FROM machine WHERE MachCode=workorder.MachCode) AS MachModel,
(SELECT RawMaterialDescription FROM rawmaterials WHERE RawMaterialCode=workorder.ProductRawMaterial) AS RawMaterialDescr,
(SELECT RawMaterialColor FROM rawmaterials WHERE RawMaterialCode=workorder.ProductRawMaterial) AS RawMaterialColor,
(SELECT StaffName FROM staff WHERE AccountName=workorder.AssignStaff) AS AssignStaffName,
(SELECT StaffCode FROM staff WHERE AccountName=workorder.AssignStaff) AS AssignStaffCode,
(SELECT MachStatus FROM machine WHERE MachCode=workorder.MachCode) AS MachStatus,
(SELECT SUM(xStopTime) FROM workorderb WHERE xWoNumber=workorder.WoNumber) AS WoTotalStopTime
FROM workorder
WHERE WoStatus=3
ORDER BY PlanProdStartDate DESC, WoSortNumber, WoNumber LIMIT 100
SELECT workorder.*,machine.MachModel,machine.MachStatus,rawmaterials.RawMaterialDescription,rawmaterials.RawMaterialColor,staff.StaffName,staff.StaffCode,SUM(workorderb.xStopTime)
FROM workorder
LEFT JOIN machine ON machine.MachCode=workorder.MachCode
LEFT JOIN rawmaterials ON rawmaterials.RawMaterialCode=workorder.ProductRawMaterial
LEFT JOIN staff ON staff.AccountName=workorder.AssignStaff
LEFT JOIN workorderb ON workorderb.xWoNumber=workorder.WoNumber
WHERE workorder.WoStatus=3
ORDER BY workorder.PlanProdStartDate DESC, workorder.WoSortNumber, workorder.WoNumber LIMIT 100
CREATE TABLE `workorder` (
`WoNumber` varchar(20) NOT NULL,
`MachCode` varchar(15) NOT NULL,
`PlannedMoldCode` varchar(10) NOT NULL,
`PartyNumber` smallint(6) NOT NULL,
`PlanProdCycleTime` smallint(6) NOT NULL,
`CalAverageCycleTime` float(15,1) unsigned NOT NULL,
`ProductRawMaterial` varchar(30) NOT NULL,
`PlanProdStartDate` date NOT NULL,
`PlanProdFinishDate` int(10) unsigned NOT NULL,
`WoStartDate` datetime DEFAULT NULL,
`WoFinishDate` datetime DEFAULT NULL,
`WoWorkTime` int(10) unsigned NOT NULL,
`WoSystemProductivity` smallint(6) unsigned NOT NULL,
`AssignStaff` varchar(50) DEFAULT '',
`WoStatus` smallint(6) NOT NULL,
`WoSortNumber` int(10) unsigned NOT NULL,
`CycleCount` int(11) unsigned NOT NULL,
`ControlDate` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`WoProductionStatus` smallint(6) NOT NULL DEFAULT '0',
`Creator` varchar(50) NOT NULL,
`Changer` varchar(50) NOT NULL,
`CreateDate` datetime NOT NULL,
PRIMARY KEY (`WoNumber`) USING BTREE,
KEY `WoNumber` (`WoNumber`) USING BTREE,
KEY `WoNumber_2` (`WoNumber`) USING BTREE,
KEY `WoNumber_3` (`WoNumber`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
CREATE TABLE `machine` (
`MachCode` varchar(15) NOT NULL,
`MachModel` varchar(30) NOT NULL,
`FirstProdDate` date NOT NULL,
`MachCapacity` smallint(6) NOT NULL,
`MachStatus` smallint(6) NOT NULL,
`NowMoldOnMach` varchar(10) NOT NULL DEFAULT '',
`NowMachOperator` varchar(50) NOT NULL DEFAULT '',
`NowWorkOrder` varchar(20) NOT NULL DEFAULT '',
`IPNumber` varchar(15) NOT NULL,
`Creator` varchar(50) NOT NULL,
`Changer` varchar(50) NOT NULL,
`ControlDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`OperatorLoginDate` datetime DEFAULT NULL,
`Message` varchar(500) DEFAULT NULL,
`MessageReaded` smallint(6) DEFAULT '0',
`StaffName` varchar(50) DEFAULT 'OSIS',
`StaffImage` varchar(255) DEFAULT '',
`StopDesc` varchar(30) DEFAULT 'OSIS',
`StopTime` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`MachCode`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
CREATE TABLE `rawmaterials` (
`RawMaterialCode` varchar(15) NOT NULL,
`RawMaterialDescription` varchar(30) NOT NULL,
`RawMaterialColor` varchar(30) NOT NULL,
PRIMARY KEY (`RawMaterialCode`) USING BTREE,
KEY `RawMaterialCode` (`RawMaterialCode`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
CREATE TABLE `staff` (
`StaffCode` varchar(15) DEFAULT NULL,
`StaffCardCode` varchar(10) DEFAULT NULL,
`StaffName` varchar(50) NOT NULL,
`StaffPassword` varchar(10) NOT NULL,
`StaffStatus` smallint(6) NOT NULL DEFAULT '2',
`StaffDateOfStart` date NOT NULL,
`StaffBirthDay` date DEFAULT NULL,
`StaffGender` varchar(5) DEFAULT NULL,
`StaffRoleA` smallint(6) NOT NULL,
`StaffEmail` varchar(100) NOT NULL,
`StaffImageLink` varchar(255) DEFAULT NULL,
`AccountName` varchar(50) NOT NULL,
`StaffRoleB` smallint(6) NOT NULL,
`StaffRoleD` smallint(6) NOT NULL,
`StaffRoleE` smallint(6) NOT NULL,
`StaffRoleC` smallint(6) NOT NULL,
`StaffRoleF` smallint(6) NOT NULL,
`StaffRoleG` smallint(6) NOT NULL,
`StaffRoleH` smallint(6) NOT NULL,
`StaffRoleI` smallint(6) NOT NULL,
`StaffRoleJ` smallint(6) NOT NULL,
`StaffRoleK` smallint(6) NOT NULL,
`StaffRoleL` smallint(6) NOT NULL,
`StaffRoleM` smallint(6) NOT NULL,
`StaffRoleN` smallint(6) NOT NULL,
`StaffConnection` smallint(6) NOT NULL DEFAULT '2',
`MachineWorked` varchar(15) DEFAULT NULL,
`WorkOrderWorked` varchar(20) DEFAULT NULL,
`StaffGroup` varchar(50) NOT NULL,
`Creator` varchar(50) NOT NULL,
`Changer` varchar(50) DEFAULT NULL,
PRIMARY KEY (`AccountName`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
CREATE TABLE `workorderb` (
`xWoNumber` varchar(20) NOT NULL,
`xMachCode` varchar(15) NOT NULL,
`xPlannedMoldCode` varchar(10) NOT NULL,
`xPartyNumber` smallint(6) NOT NULL,
`xStaffName` varchar(50) NOT NULL,
`xStopCode` smallint(6) NOT NULL,
`xStopStartTime` datetime NOT NULL,
`xStopFinishTime` datetime DEFAULT NULL,
`xStopTime` int(11) DEFAULT NULL,
PRIMARY KEY (`xMachCode`,`xStopStartTime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
CodePudding user response：
Your query with the joins was nearly there: it was just missing a GROUP BYclause.
I have replaced workorder.* with workorder.WoNumber in the SELECT and added GROUP BY workorder.WoNumber.
You can add as many columns from workorder in the SELECT as you like but you must list them in the GROUP BY.
SELECT workorder.WoNumber,machine.MachModel,machine.MachStatus,rawmaterials.RawMaterialDescription,rawmaterials.RawMaterialColor,staff.StaffName,staff.StaffCode,SUM(workorderb.xStopTime) FROM workorder LEFT JOIN machine ON machine.MachCode=workorder.MachCode LEFT JOIN rawmaterials ON rawmaterials.RawMaterialCode=workorder.ProductRawMaterial LEFT JOIN staff ON staff.AccountName=workorder.AssignStaff LEFT JOIN workorderb ON workorderb.xWoNumber=workorder.WoNumber WHERE workorder.WoStatus=3 GROUP BY workorder.WoNumber \* <<= ADD OTHER COLUMNS HERE AS NEEDED *\ ORDER BY workorder.PlanProdStartDate DESC, workorder.WoSortNumber, workorder.WoNumber LIMIT 100;
db<>fiddle here
CodePudding user response：
Use InnoDB, not MyISAM. MyISAM locks the entire table when INSERTing; InnoDB can often allow other threads to run when inserting.
Other notes
workorder has 4 identical indexes on wonumber; keep the PK, toss the rest. Note that a PRIMARY KEY is an index. Check the other tables for redundant Keys.
Do you need the mixture of DESC and ASC in ORDER BY PlanProdStartDate DESC, WoSortNumber, WoNumber? If not, there may be an optimization here.
As Kendle suggests, JOINs would be faster since there are cases where the same table is needed twice. If values might be missing, then LEFT might be useful; it won't change the performance.
Needed:
workorderb: INDEX(xWoNumber, xStopTime)
Is xStopTime an elapsed time? Or a time of day?