MySQL Forums
Forum List  »  Optimizer & Parser

Optimizing a query with a NULL
Posted by: Ben Holness
Date: August 22, 2011 11:04AM

Hi all,

I have a query as follows:

SELECT Invoices . * , DATE_FORMAT( Invoices.InvoiceDate, '%m/%d/%Y' ) AS NiceDate, Sites.SiteName
FROM Invoices
LEFT JOIN Jobs ON Jobs.InvoiceID = Invoices.InvoiceID
LEFT JOIN Sites ON Sites.SiteID = Jobs.SiteID
WHERE Invoices.InvoiceStatus != 'Deleted'

This takes a long time to run (around 72 seconds - see create tables auto increment values for an idea of the number of rows in each table). The EXPLAIN says:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Invoices ALL NULL NULL NULL NULL 4804 Using where
1 SIMPLE Jobs ALL Invoices NULL NULL NULL 6101
1 SIMPLE Sites eq_ref PRIMARY PRIMARY 4 SES.Jobs.SiteID 1

if I add a clause [AND Invoices.InvoiceID!='void'] then it runs in 0.0006 seconds, but I don't get the invoices that aren't associated with any jobs.

Is there any way I can optimize this so that it shows the invoices with no jobs but still runs quickly? Create table statements (slightly stripped) below, so you can see what I currently have.

Cheers,

Ben


--
-- Table structure for table `Invoices`
--

CREATE TABLE `Invoices` (
`InvoiceID` int(11) NOT NULL AUTO_INCREMENT,
`InvoiceStatus` enum('Paid','Not Paid','Deleted') NOT NULL,
`LastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`CreationDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`InvoiceDate` date NOT NULL,
`InvoiceNumber` varchar(64) NOT NULL,
`InvoiceAmount` decimal(20,2) NOT NULL,
`PreparedBy` varchar(200) NOT NULL,
PRIMARY KEY (`InvoiceID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4805 ;

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

--
-- Table structure for table `Jobs`
--

CREATE TABLE `Jobs` (
`JobID` int(11) NOT NULL AUTO_INCREMENT,
`UserID` int(11) NOT NULL,
`SiteID` int(11) NOT NULL,
`InvoiceID` varchar(32) DEFAULT NULL,
`Notes` text,
PRIMARY KEY (`JobID`),
KEY `Invoices` (`InvoiceID`(8))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6102 ;

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

--
-- Table structure for table `Sites`
--

CREATE TABLE `Sites` (
`SiteID` int(11) NOT NULL AUTO_INCREMENT,
`Status` enum('Active','Deleted') NOT NULL DEFAULT 'Active',
`RegionID` smallint(6) NOT NULL,
`SiteName` varchar(255) NOT NULL,
`Address` text NOT NULL,
PRIMARY KEY (`SiteID`),
KEY `SiteNameFilter` (`SiteName`(16))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=16915 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizing a query with a NULL
2269
August 22, 2011 11:04AM
965
August 23, 2011 08:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.