Skip to content
Advertisement

Optimizing mysql query for better performance to get low execution time

Currently I have this mysql query in my php code. I want to optimize it better because this query is currently inside a while loop and this query alone takes 25 seconds. so the bigger the loop the time it takes to execute this query over and over starts to become insane in only a short time. So I would love to get some help regarding this.

$select_count = "select site_id, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Nozzle = 1) IS_Nozzle, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Electronic_Components = 1) IS_Electronic_Components, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Power_device = 1) IS_Power_device, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Consumable = 1) IS_Consumable, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Hose = 1) IS_Hose, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Switch = 1) IS_Switch, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Valve = 1) IS_Valve, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Housing_Frame = 1) IS_Housing_Frame, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Pump = 1) IS_Pump, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Totalizer = 1) IS_Totalizer, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Flow_Meter = 1) IS_Flow_Meter, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Swivel = 1) IS_Swivel, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Submersible = 1) IS_Submersible, "
                . "(select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '$from' AND '$to') AND "
                . "Site_ID = $site_id and IS_Other = 1) IS_Other "
                . "FROM Site_detail x "
                . "WHERE is_maintenance = 1";

Edit

This is the sql query

select site_id
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Nozzle = 1) IS_Nozzle
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Electronic_Components = 1) IS_Electronic_Components
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Power_device = 1) IS_Power_device
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Consumable = 1) IS_Consumable
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Hose = 1) IS_Hose
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Switch = 1) IS_Switch
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Valve = 1) IS_Valve
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Housing_Frame = 1) IS_Housing_Frame
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Pump = 1) IS_Pump
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Totalizer = 1) IS_Totalizer
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Flow_Meter = 1) IS_Flow_Meter
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Swivel = 1) IS_Swivel
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Submersible = 1) IS_Submersible
     , (select count(id) from Tbl_WO_Detail WHERE Description != 'PM' AND (WO_Date between '2020-12-1' AND '2020-12-2') AND Site_ID = 1342 and IS_Other = 1) IS_Other 
  FROM Site_detail x 
 WHERE is_maintenance = 1

And for the table site_details

CREATE TABLE `site_detail` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `site_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `site_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `owner_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `owner_phone` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `commission_date` date DEFAULT NULL,
 `noofpressure_unit` bigint(20) DEFAULT NULL,
 `noofsuction_unit` bigint(20) DEFAULT NULL,
 `region_id` bigint(20) DEFAULT NULL,
 `oilcompany_id` bigint(20) DEFAULT NULL,
 `subregion_id` bigint(20) DEFAULT NULL,
 `natures` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `is_maintenance` tinyint(4) DEFAULT NULL,
 `site_no` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Asset_Qty` bigint(20) unsigned DEFAULT NULL,
 `TimeStamp` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=46712 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC

CREATE TABLE `tbl_wo_detail` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `WO_No` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Caller_No` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Contact_Person` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `WO_Date` date DEFAULT NULL,
 `WO_Time` time DEFAULT NULL,
 `OC_Date` date DEFAULT NULL,
 `OC_Time` time DEFAULT NULL,
 `OC_ID` int(11) DEFAULT NULL,
 `Complain_No` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Description` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Site_ID` int(11) DEFAULT NULL,
 `Subregion` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Target_Date` date DEFAULT NULL,
 `Target_Time` time DEFAULT NULL,
 `WO_Priority` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `WO_Status` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Down_Time` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Remarks` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Task_Mode` varchar(50) COLLATE utf8_unicode_ci DEFAULT 'Open',
 `Detail_Other` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Solution` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Finish_Date` date DEFAULT NULL,
 `Finish_T1` time DEFAULT NULL,
 `Finish_T2` time DEFAULT NULL,
 `Pay_Mode` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `PO_Number` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Customer_Name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Customer_Comments` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `Prd_Qty` int(11) DEFAULT NULL,
 `Tech_Qty` int(11) DEFAULT NULL,
 `MRRNo` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `IS_Nozzle` tinyint(4) DEFAULT 0,
 `IS_Electronic_Components` tinyint(4) DEFAULT 0,
 `IS_Power_device` tinyint(4) DEFAULT 0,
 `IS_Consumable` tinyint(4) DEFAULT 0,
 `IS_Hose` tinyint(4) DEFAULT 0,
 `IS_Switch` tinyint(4) DEFAULT 0,
 `IS_Valve` tinyint(4) DEFAULT 0,
 `IS_Housing_Frame` tinyint(4) DEFAULT 0,
 `IS_Pump` tinyint(4) DEFAULT 0,
 `IS_Totalizer` tinyint(4) DEFAULT 0,
 `IS_Flow_Meter` tinyint(4) DEFAULT 0,
 `IS_Swivel` tinyint(4) DEFAULT 0,
 `IS_Preventive_Maintenance` tinyint(4) DEFAULT 0,
 `IS_Submersible` tinyint(4) DEFAULT 0,
 `IS_Other` tinyint(4) DEFAULT 0,
 `Edit_DateTime` datetime DEFAULT NULL,
 `chkVIS` tinyint(4) DEFAULT 0,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=444908 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC

Advertisement

Answer

Looks like

SELECT site_id, 
       SUM(IS_Nozzle = 1) IS_Nozzle, 
       SUM(IS_Electronic_Components = 1) IS_Electronic_Components, 
       SUM(IS_Power_device = 1) IS_Power_device, 
       SUM(IS_Consumable = 1) IS_Consumable, 
       SUM(IS_Hose = 1) IS_Hose, 
       SUM(IS_Switch = 1) IS_Switch, 
       SUM(IS_Valve = 1) IS_Valve, 
       SUM(IS_Housing_Frame = 1) IS_Housing_Frame, 
       SUM(IS_Pump = 1) IS_Pump, 
       SUM(IS_Totalizer = 1) IS_Totalizer, 
       SUM(IS_Flow_Meter = 1) IS_Flow_Meter, 
       SUM(IS_Swivel = 1) IS_Swivel, 
       SUM(IS_Submersible = 1) IS_Submersible, 
       SUM(IS_Other = 1) IS_Other 
FROM Site_detail x 
JOIN Tbl_WO_Detail y USING (site_id)
WHERE x.is_maintenance = 1
  AND y.Description != 'PM' 
  AND y.WO_Date between '$from' AND '$to'
GROUP BY site_id;

If the columns used in the conditions (IS_Nozzle, IS_Electronic_Components, etc.) are boolean columns (contains only 0 or 1) then = 1 may be removed.

PS. In the last case I’d recommend to use one column of SET datatype instead of a lot of separate columns.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement