CODEWITHSUNDEEP
phpmysql
Dave
Dave

Reputation: 3288

SQL query optimisation advice

I've got a weird situation where a couple of sql queries + some simple php maths are taking upwards of 30 seconds + to execute. I know the issue lies with the sql queries but I can't for the life of me think of a way to rewrite/optimise the queries.

Typically I'd swap to joins but because its a double sub select with maths I can't think of a way of doing it better. Am I going down the right route doing it all in query or should I do 4 simple count selects and do all the maths in php.

Index's are built on all columns used in the where clauses.

Query 1

SELECT COUNT(1) FROM tblcontainergroups WHERE 
  (
        (SELECT COUNT(1) FROM tblmovements WHERE TicketStatus=1 AND MovementType=0 AND GroupID=tblcontainergroups.ID)
        -
        (SELECT COUNT(1) FROM tblmovements WHERE TicketStatus=1 AND MovementType=2 AND GroupID=tblcontainergroups.ID)
    )>0

Query 2

SELECT COUNT(1) FROM tblcontainergroups WHERE 
  (
        (SELECT COUNT(1) FROM tblmovements WHERE TicketStatus=1 AND MovementType=0 AND GroupID=tblcontainergroups.ID)
        -
        (SELECT COUNT(1) FROM tblmovements WHERE TicketStatus=1 AND MovementType=2 AND GroupID=tblcontainergroups.ID)
    )>0 AND LENGTH(DateOfIncreases)>4

Then the php is a simple percentage of those 2 figures

$percentage = number_format(($total_with_increases/$total_active_groups)*100,2);

I know this is probably a really simple question and I'm probably being an idiot but is there a better way of doing this? can the queries be rewritten to not be so god damn slow.

Table Structures

--
-- Table structure for table `tblcontainergroups`
--

CREATE TABLE `tblcontainergroups` (
`ID` bigint(20) unsigned NOT NULL,
  `ContainerType` bigint(20) NOT NULL DEFAULT '0',
  `WasteType` bigint(20) NOT NULL DEFAULT '0',
  `IsHazardous` tinyint(1) NOT NULL DEFAULT '0',
  `PONumber` varchar(50) DEFAULT NULL,
  `SiteID` bigint(20) NOT NULL DEFAULT '0',
  `CreatedBy` int(10) NOT NULL DEFAULT '0',
  `CreatedOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `SICProducer` varchar(500) DEFAULT NULL,
  `HasRentals` tinyint(1) NOT NULL DEFAULT '0',
  `DistanceText` varchar(20) DEFAULT NULL,
  `DistanceMeters` int(10) NOT NULL DEFAULT '0',
  `IncreaseTrans` decimal(10,2) NOT NULL DEFAULT '0.00',
  `IncreaseTonnage` decimal(10,2) NOT NULL DEFAULT '0.00',
  `IncreaseWLHour` decimal(10,2) NOT NULL DEFAULT '0.00',
  `IncreasesCompleted` tinyint(1) NOT NULL DEFAULT '0',
  `DateOfIncreases` datetime DEFAULT NULL,
  `VariousWasteTypes` text,
  `PremRequired` tinyint(1) NOT NULL DEFAULT '0',
  `PremDescription` text
) ENGINE=InnoDB AUTO_INCREMENT=109454 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `tblmovements`
--

CREATE TABLE `tblmovements` (
`ID` bigint(20) unsigned NOT NULL,
  `ClientID` bigint(20) unsigned NOT NULL,
  `SiteID` bigint(20) unsigned NOT NULL,
  `GroupID` bigint(20) NOT NULL DEFAULT '0',
  `PONumber` varchar(50) DEFAULT NULL,
  `MovementType` smallint(3) NOT NULL DEFAULT '0',
  `WLHours` decimal(6,2) NOT NULL DEFAULT '0.00',
  `Supplier` bigint(20) NOT NULL DEFAULT '0',
  `OrderedBy` varchar(200) DEFAULT NULL,
  `SupplierNotes` text,
  `DateRequired` datetime DEFAULT NULL,
  `SuppTransCharge` decimal(10,2) NOT NULL DEFAULT '0.00',
  `TransMarkup` decimal(10,2) NOT NULL DEFAULT '0.00',
  `TransClientCharge` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SuppWeightRate` decimal(10,2) NOT NULL DEFAULT '0.00',
  `WeightMarkup` decimal(10,2) NOT NULL DEFAULT '0.00',
  `ClientWeightCharge` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SupplierIncMinTonnes` decimal(10,2) NOT NULL DEFAULT '0.00',
  `ClientIncMinTonnes` decimal(10,2) NOT NULL DEFAULT '0.00',
  `TonnesClientCharge` decimal(10,2) NOT NULL DEFAULT '0.00',
  `TonneageType` smallint(3) NOT NULL DEFAULT '0',
  `SuppWLRate` decimal(10,2) NOT NULL DEFAULT '0.00',
  `WLMarkup` decimal(10,2) NOT NULL DEFAULT '0.00',
  `WLClientRate` decimal(10,2) NOT NULL DEFAULT '0.00',
  `CreatedBy` int(10) NOT NULL DEFAULT '0',
  `CreatedOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `TicketStatus` smallint(3) NOT NULL DEFAULT '1',
  `IsInvoiced` tinyint(1) NOT NULL DEFAULT '0',
  `GGOrderNumber` bigint(20) NOT NULL DEFAULT '0',
  `OrderSubmitted` tinyint(1) NOT NULL DEFAULT '0',
  `ParentTicket` bigint(20) unsigned NOT NULL DEFAULT '0',
  `SuppWLHours` decimal(5,2) NOT NULL DEFAULT '0.00',
  `ClientWLHours` decimal(5,2) NOT NULL DEFAULT '0.00',
  `WasteDestination` int(10) NOT NULL DEFAULT '0',
  `TicketWeight` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `VHCReg` varchar(10) DEFAULT NULL,
  `SupplierContact` varchar(200) DEFAULT NULL,
  `ComplianceNotes` text,
  `WeightAddedBy` int(10) NOT NULL DEFAULT '0',
  `RateSetBy` int(10) NOT NULL DEFAULT '0',
  `IsCompleted` tinyint(1) NOT NULL DEFAULT '0',
  `DateWeightAdded` datetime DEFAULT NULL,
  `RebateRate` decimal(10,2) NOT NULL DEFAULT '0.00',
  `InvoiceNumber` varchar(50) DEFAULT NULL,
  `IsPaid` tinyint(1) NOT NULL DEFAULT '0',
  `InvoiceDate` datetime DEFAULT NULL,
  `ActualRebate` decimal(20,2) NOT NULL DEFAULT '0.00',
  `RebateToClient` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SupplierWeightContact` varchar(200) DEFAULT NULL,
  `VHCAddedBy` int(10) NOT NULL DEFAULT '0',
  `OldTicketNumber` varchar(20) DEFAULT NULL,
  `BelongsToWR` bigint(20) NOT NULL DEFAULT '0',
  `OkInvoice` tinyint(1) NOT NULL DEFAULT '0',
  `DeletionDate` datetime DEFAULT NULL,
  `DeletedBy` int(10) NOT NULL DEFAULT '0',
  `SupplierInvNo` varchar(100) DEFAULT NULL,
  `SuppTicketNo` varchar(100) DEFAULT NULL,
  `SuppIsDisputed` tinyint(1) NOT NULL DEFAULT '0',
  `SuppDateDisputed` datetime DEFAULT NULL,
  `SuppDisputedBy` int(10) NOT NULL DEFAULT '0',
  `SuppDateDisputeCleared` datetime DEFAULT NULL,
  `SuppDisputeClearedBy` int(10) NOT NULL DEFAULT '0',
  `SuppIsPaid` tinyint(1) NOT NULL DEFAULT '0',
  `SuppPaidOn` datetime DEFAULT NULL,
  `SuppPaidBy` int(10) NOT NULL DEFAULT '0',
  `TicketApproveReason` text,
  `ComplianceCopyTicket` tinyint(1) NOT NULL DEFAULT '0',
  `SuppInvCheckedBy` int(10) NOT NULL DEFAULT '0',
  `SuppCheckDate` datetime DEFAULT NULL,
  `SupplierOrderConfirmedBy` text,
  `OrderConfirmationDate` datetime DEFAULT NULL,
  `OrderConfirmedUser` int(10) NOT NULL DEFAULT '0',
  `TicketReceived` tinyint(1) NOT NULL DEFAULT '0',
  `DisputeReason` text,
  `DisputeType` smallint(5) NOT NULL DEFAULT '0',
  `DisputeClearReason` text,
  `ClientIsDisputed` tinyint(1) NOT NULL DEFAULT '0',
  `ClientDateDisputed` datetime DEFAULT NULL,
  `ClientDisputedBy` int(10) NOT NULL DEFAULT '0',
  `ClientDisputeReason` text,
  `ClientDisputeType` smallint(5) NOT NULL DEFAULT '0',
  `ClientDateDisputeCleared` datetime DEFAULT NULL,
  `ClientDisputeClearedBy` int(10) NOT NULL DEFAULT '0',
  `ClientDisputeClearReason` text,
  `CarbonUsageKGKM` decimal(20,4) NOT NULL DEFAULT '0.0000',
  `CarbonUsageKGL` decimal(20,4) NOT NULL DEFAULT '0.0000',
  `SuppTonneageType` smallint(3) NOT NULL DEFAULT '0',
  `TicketApprovedBy` int(10) NOT NULL DEFAULT '0',
  `TicketApprovedDate` datetime DEFAULT NULL,
  `IsLate` tinyint(1) NOT NULL DEFAULT '0',
  `RoRoProcessed` tinyint(1) NOT NULL DEFAULT '0',
  `LeadOwnerBDM` int(10) NOT NULL DEFAULT '0',
  `LastComplianceUpdate` datetime DEFAULT NULL,
  `IsDummy` tinyint(4) DEFAULT '0',
  `DisputerName` text,
  `DisputerContact` text,
  `ClientDisputerName` text,
  `ClientDisputerContact` text,
  `MarginsProcessed` tinyint(1) NOT NULL DEFAULT '0',
  `OrigCreator` int(10) NOT NULL DEFAULT '0',
  `AccountsSent` tinyint(1) NOT NULL DEFAULT '0',
  `NoRebate` tinyint(1) NOT NULL DEFAULT '0',
  `WTNCreated` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=368401 DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblcontainergroups`
--
ALTER TABLE `tblcontainergroups`
 ADD PRIMARY KEY (`ID`), ADD KEY `ContainerType` (`ContainerType`), ADD KEY `WasteType` (`WasteType`);

--
-- Indexes for table `tblmovements`
--
ALTER TABLE `tblmovements`
 ADD PRIMARY KEY (`ID`), ADD KEY `ClientID` (`ClientID`), ADD KEY `SiteID` (`SiteID`), ADD KEY `GroupID` (`GroupID`), ADD KEY `Supplier` (`Supplier`), ADD KEY `OrderedBy` (`OrderedBy`), ADD KEY `CreatedBy` (`CreatedBy`), ADD KEY `GGOrderNumber` (`GGOrderNumber`), ADD KEY `WasteDestination` (`WasteDestination`), ADD KEY `WeightAddedBy` (`WeightAddedBy`), ADD KEY `RateSetBy` (`RateSetBy`), ADD KEY `VHCAddedBy` (`VHCAddedBy`), ADD KEY `BelongsToWR` (`BelongsToWR`), ADD KEY `DeletedBy` (`DeletedBy`), ADD KEY `SupplierInvNo` (`SupplierInvNo`), ADD KEY `SuppDisputedBy` (`SuppDisputedBy`), ADD KEY `SuppDisputeClearedBy` (`SuppDisputeClearedBy`), ADD KEY `SuppPaidBy` (`SuppPaidBy`), ADD KEY `SuppInvCheckedBy` (`SuppInvCheckedBy`), ADD KEY `OrderConfirmedUser` (`OrderConfirmedUser`), ADD KEY `DisputeType` (`DisputeType`), ADD KEY `TicketApprovedBy` (`TicketApprovedBy`), ADD KEY `ClientDisputeClearedBy` (`ClientDisputeClearedBy`), ADD KEY `ClientDisputedBy` (`ClientDisputedBy`), ADD KEY `LeadOwnerBDM` (`LeadOwnerBDM`), ADD KEY `m_disputed` (`SuppIsDisputed`), ADD KEY `m_clientdisputed` (`ClientIsDisputed`), ADD KEY `OrigCreator` (`OrigCreator`), ADD KEY `TicketStatus` (`TicketStatus`), ADD KEY `MovementType` (`MovementType`);

Upvotes: 0

Views: 78

Answers (1)

smozgur
smozgur

Reputation: 1812

It might be a simple question, however it is hard for me since I can't see the model logic that you are trying to build.

Still, following should be the optimized version of your query, according to me:

SELECT COUNT(1) 
FROM tblcontainergroups 
WHERE (SELECT SUM(IF(MovementType=0,1,IF(MovementType=2, -1, 0)))
    FROM tblmovements 
    WHERE GroupID=tblcontainergroups.ID AND TicketStatus=1)>0

Basically, you need only one sub query with conditional SUM that will sum 1s or -1s according to MovementType value, and criteria for the sub query for GroupID and TicketStatus.

Also, you need to create index for the GroupID, MovementType and TicketStatus fields (separately) in the tblmovements table.

Upvotes: 2

Related Questions