Forum Moderators: coopster
Is there an issue/incompatibility with the php_mysqli.dll and/or libmysql.dll supplied with Windows PHP 5.2.9 and MySQL 5.1.32? If so, are there alternatives?
Queries 1-3 below just represent "drill downs" on a data set. Why is Query #3 so painfully slow in PHP (but no problem in MySQL)?
=========================================
ENVIRONMENT
=========================================
PHP Version 5.2.9-2
Windows Server 2003 R2 Standard Edition SP2
IIS 6/ISAPI
MySQLi Client API library version 5.0.51a
MySQLi Client API header version 5.0.51a
MYSQLI_SOCKET /tmp/mysql.sock
MySQL Server 5.1.32-community (cache on demand)
phpMyAdmin - 2.11.5.1 (using mysqli extension)
=========================================
QUERY 1:
=========================================
SELECT sources.MediaType AS `mediatype` , SUM( IF( items.Status
IN (
'Declined', 'Auto Declined'
), 0, ifnull( (
items.Qty * items.Price
) + items.shipping + items.Tax, 0 ) ) ) AS `totalsalessh` , COUNT( DISTINCT IF( calldetail.Answered, calldetail.CallID, NULL ) ) AS `callsanswered` , COUNT( DISTINCT IF( calldetail.ShortCall, calldetail.CallID, NULL ) ) AS `callsdropivr` , COUNT( DISTINCT IF( calldetail.DNIS = '', NULL , calldetail.CallID ) ) AS `callsreceived` , COUNT( DISTINCT IF( calldetail.TransferredCall, calldetail.CallID, NULL ) ) AS `callstransferred`
FROM calldetail
INNER JOIN sources ON calldetail.SourceID = sources.SourceID
INNER JOIN supplier ON sources.SupplierID = supplier.SupplierID
LEFT JOIN orders ON calldetail.CallID = orders.CallID
LEFT JOIN items ON orders.OrderID = items.OrderID
WHERE calldetail.InitiatedDate
BETWEEN '2009-05-14 00:00:00'
AND '2009-05-14 23:59:59'
GROUP BY 1
WITH ROLLUP
=========================================
Performance:
(29 Records)
phpMyAdmin: 0.1610 seconds
MySQL Query Browser 1.2.12: 0.1619 seconds
MySQL Command Line Client: 1.53 seconds
=========================================
QUERY 2:
=========================================
SELECT supplier.SupplierID AS `supplierid` , supplier.SupplierName AS `supplier` , SUM( IF( items.Status
IN (
'Declined', 'Auto Declined'
), 0, ifnull( (
items.Qty * items.Price
) + items.shipping + items.Tax, 0 ) ) ) AS `totalsalessh` , COUNT( DISTINCT IF( calldetail.Answered, calldetail.CallID, NULL ) ) AS `callsanswered` , COUNT( DISTINCT IF( calldetail.ShortCall, calldetail.CallID, NULL ) ) AS `callsdropivr` , COUNT( DISTINCT IF( calldetail.DNIS = '', NULL , calldetail.CallID ) ) AS `callsreceived` , COUNT( DISTINCT IF( calldetail.TransferredCall, calldetail.CallID, NULL ) ) AS `callstransferred`
FROM calldetail
INNER JOIN sources ON calldetail.SourceID = sources.SourceID
INNER JOIN supplier ON sources.SupplierID = supplier.SupplierID
LEFT JOIN orders ON calldetail.CallID = orders.CallID
LEFT JOIN items ON orders.OrderID = items.OrderID
WHERE sources.MediaType = 'LF TV'
AND calldetail.InitiatedDate
BETWEEN '2009-05-14 00:00:00'
AND '2009-05-14 23:59:59'
GROUP BY 1
WITH ROLLUP
=========================================
Performance:
(3 Records)
phpMyAdmin: 0.0370 seconds
MySQL Query Browser 1.2.12: 0.0360 seconds
MySQL Command Line Client: 0.05 seconds
=========================================
QUERY 3:
=========================================
SELECT calldetail.InitiatedDate AS `calldatetime`, SUM(IF(items.Status
IN ('Declined','Auto Declined'),0,ifnull((
items.Qty * items.Price
) + items.shipping + items.Tax,0))) AS `totalsalessh`, COUNT(DISTINCT IF(calldetail.Answered, calldetail.CallID, NULL)) AS `callsanswered`, COUNT(DISTINCT IF(calldetail.ShortCall, calldetail.CallID, NULL)) AS `callsdropivr`, COUNT(DISTINCT IF(calldetail.DNIS = '', NULL, calldetail.CallID)) AS `callsreceived`, COUNT(DISTINCT IF(calldetail.TransferredCall, calldetail.CallID, NULL)) AS `callstransferred`, calldetail.CallID AS `call`, IFNULL(orders.OrderID, '') AS `order`
FROM calldetail
INNER JOIN sources ON calldetail.SourceID = sources.SourceID
INNER JOIN supplier ON sources.SupplierID = supplier.SupplierID
LEFT JOIN orders ON calldetail.CallID = orders.CallID
LEFT JOIN items ON orders.OrderID = items.OrderID
WHERE sources.MediaType = 'LF TV'
AND supplier.SupplierID = '27264066900148'
AND calldetail.InitiatedDate
BETWEEN '2009-05-14 00:00:00' AND '2009-05-14 23:59:59'
GROUP BY 7
WITH ROLLUP
=========================================
Performance:
(5 Records)
phpMyAdmin: 44.145 seconds
MySQL Query Browser 1.2.12: 0.4937 seconds
MySQL Command Line Client: 0.05 seconds
+++++++++++++++++++++++++++++++++++++++++
DUMP OF SQL STRUCTURE
+++++++++++++++++++++++++++++++++++++++++
-- phpMyAdmin SQL Dump
-- version 2.11.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 15, 2009 at 07:59 AM
-- Server version: 5.1.32
-- PHP Version: 5.2.9-2
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `sql_reports`
--
-- --------------------------------------------------------
--
-- Table structure for table `callctr`
--
CREATE TABLE IF NOT EXISTS `callctr` (
`active` tinyint(4) NOT NULL,
`CallCtrName` varchar(45) NOT NULL,
`DateCreate` date NOT NULL,
`CallCtrID` varchar(25) NOT NULL,
PRIMARY KEY (`CallCtrID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `calldetail`
--
CREATE TABLE IF NOT EXISTS `calldetail` (
`CallId` varchar(25) NOT NULL,
`CallDirection` varchar(20) NOT NULL,
`LocalUserId` varchar(50) NOT NULL,
`LocalNumber` varchar(25) NOT NULL,
`LocalName` varchar(50) NOT NULL,
`RemoteNumber` varchar(15) NOT NULL,
`RemoteName` varchar(50) NOT NULL,
`InitiatedDate` datetime NOT NULL,
`ConnectedDate` datetime NOT NULL,
`LineDurationSeconds` int(11) NOT NULL,
`DNIS` varchar(50) NOT NULL,
`CustomNum1` int(11) NOT NULL,
`CustomNum2` int(11) NOT NULL,
`CustomNum3` int(11) NOT NULL,
`CustomString1` varchar(50) NOT NULL,
`CustomString2` varchar(50) NOT NULL,
`CustomString3` varchar(50) NOT NULL,
`CallCtrID` varchar(25) NOT NULL,
`Terminateddate` datetime NOT NULL,
`SourceID` varchar(15) NOT NULL,
`ShortCall` tinyint(4) NOT NULL,
`TransferredCall` tinyint(4) NOT NULL,
`LogID` varchar(15) NOT NULL,
`Answered` tinyint(4) NOT NULL,
`TalkTime` time NOT NULL,
`TalkTime_Sec` smallint(6) NOT NULL DEFAULT '0',
`WaitTime` time NOT NULL,
`WaitTime_Sec` smallint(6) NOT NULL DEFAULT '0',
`StationID` varchar(20) NOT NULL,
`SupplierNo` varchar(45) NOT NULL,
`Agency` varchar(45) NOT NULL,
`station` varchar(45) NOT NULL,
`abandoned` tinyint(45) NOT NULL,
`wrapupcode` varchar(45) NOT NULL,
`AgencyProductCode` varchar(45) NOT NULL,
`AgencyCampaignCode` varchar(45) NOT NULL,
`AgencyCampaignCode2` varchar(45) NOT NULL,
PRIMARY KEY (`CallId`),
KEY `initiatedate` (`InitiatedDate`),
KEY `SourceID` (`SourceID`),
KEY `LocalNumber` (`LocalNumber`),
KEY `CallCtrID` (`CallCtrID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `customers`
--
CREATE TABLE IF NOT EXISTS `customers` (
`Address1` varchar(45) NOT NULL,
`Address2` varchar(45) NOT NULL,
`City` varchar(45) NOT NULL,
`State` varchar(2) NOT NULL,
`Country` varchar(2) NOT NULL,
`CallID` varchar(25) NOT NULL,
`CustomerID` varchar(15) NOT NULL,
`DateCreated` date NOT NULL,
`Email` varchar(60) NOT NULL,
`FirstName` varchar(45) NOT NULL,
`LastName` varchar(45) NOT NULL,
`Telephone` varchar(15) NOT NULL,
`KeyCode` varchar(45) NOT NULL,
`Lead` tinyint(4) NOT NULL,
`LeadReason` varchar(45) NOT NULL,
`LeadType` varchar(45) NOT NULL,
`MI` varchar(10) NOT NULL,
`ZipPlusFour` varchar(10) NOT NULL,
`OriginalSourceID` varchar(15) NOT NULL,
`LogID` varchar(15) NOT NULL,
`supplierNo` varchar(45) NOT NULL,
`EmployeeNo` varchar(45) NOT NULL,
`Maildate` date NOT NULL,
`CampaignCode` varchar(45) NOT NULL,
`CreateTS` datetime NOT NULL,
`ListName` varchar(60) NOT NULL,
PRIMARY KEY (`CustomerID`),
KEY `FirstName` (`FirstName`),
KEY `LastName` (`LastName`),
KEY `CreateTS` (`CreateTS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `employee`
--
CREATE TABLE IF NOT EXISTS `employee` (
`Active` tinyint(4) NOT NULL,
`DateofHire` date NOT NULL,
`EmployeeID` varchar(15) NOT NULL,
`EmployeeNo` varchar(25) NOT NULL,
`First` varchar(45) NOT NULL,
`Last` varchar(45) NOT NULL,
`SerialTS` datetime NOT NULL,
`Team` varchar(45) NOT NULL,
`Email` varchar(45) NOT NULL,
`LogID` varchar(25) NOT NULL,
`DateTerminated` date NOT NULL,
PRIMARY KEY (`EmployeeID`),
UNIQUE KEY `EmployeeNo` (`EmployeeNo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `items`
--
CREATE TABLE IF NOT EXISTS `items` (
`AccountNo` varchar(45) NOT NULL,
`Approved` tinyint(4) NOT NULL,
`AutoShip` tinyint(4) NOT NULL,
`AutoShipDays` int(11) NOT NULL,
`BankName` varchar(90) NOT NULL,
`CallID` varchar(25) NOT NULL,
`CardNo` varchar(16) NOT NULL,
`CCExpDate` varchar(4) NOT NULL,
`CheckNo` varchar(6) NOT NULL,
`CustomerID` varchar(25) NOT NULL,
`DateCreate` date NOT NULL,
`DateOrder` date NOT NULL,
`DateProcessed` date NOT NULL,
`DateShipped` date NOT NULL,
`EmployeeID` varchar(25) NOT NULL,
`ItemCode` varchar(45) NOT NULL,
`ItemID` varchar(25) NOT NULL,
`ModTS` datetime NOT NULL,
`OptionCode` varchar(45) NOT NULL,
`OrderID` varchar(25) NOT NULL,
`OutboundCallID` varchar(25) NOT NULL,
`Price` decimal(9,2) NOT NULL,
`ProductID` varchar(25) NOT NULL,
`OptionID` varchar(25) NOT NULL,
`Qty` tinyint(4) NOT NULL,
`RountingNo` varchar(25) NOT NULL,
`Rush` tinyint(4) NOT NULL,
`SerialTS` datetime NOT NULL,
`ShipID` varchar(25) NOT NULL,
`SourceID` varchar(25) NOT NULL,
`Status` varchar(25) NOT NULL,
`SupplierID` varchar(25) NOT NULL,
`Tax` decimal(9,2) NOT NULL,
`LogID` varchar(25) NOT NULL,
`shipping` decimal(9,2) NOT NULL,
`crossell` tinyint(4) NOT NULL,
`supplierno` varchar(45) NOT NULL,
`team` varchar(45) NOT NULL,
`club` tinyint(4) NOT NULL,
`EmployeeNo` varchar(45) NOT NULL,
`ProductCode` varchar(45) NOT NULL,
`SupplierSKU` varchar(25) NOT NULL,
`PaymentMethod` varchar(25) NOT NULL,
`OMSPrice` decimal(9,2) NOT NULL,
PRIMARY KEY (`ItemID`),
KEY `OrderID` (`OrderID`),
KEY `status` (`Status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `orders`
--
CREATE TABLE IF NOT EXISTS `orders` (
`CallID` varchar(25) NOT NULL,
`CustomerID` varchar(25) NOT NULL,
`DateCreate` datetime NOT NULL,
`EmployeeID` varchar(25) NOT NULL,
`EnteredBy` varchar(15) NOT NULL,
`OrderID` varchar(25) NOT NULL,
`OrderNo` varchar(25) NOT NULL,
`OrderStatus` varchar(45) NOT NULL,
`OriginalEmpNum` varchar(25) NOT NULL,
`ScriptID` varchar(25) NOT NULL,
`SourceID` varchar(25) NOT NULL,
`SupplierID` varchar(25) NOT NULL,
`LogID` varchar(25) NOT NULL,
`CallCtrID` varchar(25) NOT NULL,
`ModTS` datetime NOT NULL COMMENT 'OMS Mod TS',
`PaymentMethod` varchar(25) NOT NULL,
`ManualOrderReason` varchar(50) NOT NULL,
PRIMARY KEY (`OrderID`),
KEY `CallID` (`CallID`),
KEY `CustomerID` (`CustomerID`),
KEY `EmployeeID` (`EmployeeID`),
KEY `ModTS` (`ModTS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `sources`
--
CREATE TABLE IF NOT EXISTS `sources` (
`Active` tinyint(4) NOT NULL,
`Agency` varchar(45) NOT NULL,
`ClientDNS` varchar(45) NOT NULL,
`DateActive` date NOT NULL,
`DateAssigned` date NOT NULL,
`DateDeactived` date NOT NULL,
`MediaType` varchar(45) NOT NULL,
`ModTS` datetime NOT NULL,
`ProductCode` varchar(45) NOT NULL,
`ProductID` varchar(25) NOT NULL,
`SourceID` varchar(25) NOT NULL,
`SourceType` varchar(45) NOT NULL,
`SupplierID` varchar(25) NOT NULL,
`Telephone` varchar(15) NOT NULL,
`Source` varchar(45) NOT NULL,
`DateCreated` date NOT NULL,
`LogID` varchar(25) NOT NULL,
`MasterMediaType` varchar(25) NOT NULL,
`MasterClient` varchar(25) NOT NULL,
`SourceName` varchar(45) NOT NULL,
PRIMARY KEY (`SourceID`),
KEY `SupplierID` (`SupplierID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `supplier`
--
CREATE TABLE IF NOT EXISTS `supplier` (
`Active` tinyint(4) NOT NULL,
`Email` varchar(45) NOT NULL,
`SupplierID` varchar(25) NOT NULL,
`SupplierName` varchar(45) NOT NULL,
`ModTS` datetime NOT NULL,
`SerialTS` datetime NOT NULL,
`SupplierNo` varchar(45) NOT NULL,
`LogID` varchar(25) NOT NULL,
PRIMARY KEY (`SupplierID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
Have you used EXPLAIN [dev.mysql.com] on the query to determine bottlenecks?
