Optimizing a query with a NULL

[Ben Holness]

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 ;