StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSlow MySQL query using sub select and IF conditionals
text
Body
copied!<p>I'm having a little problem and would like some advice.<br> What I want to do is select everything from tableA where there is an entry in tableB WHERE the entry in tableB is the first entry within the table and that entry is within a specific date range.</p> <p>So with an start date of 2013-01-01 00:00:00 and an end date of 2013-01-31 23:59:59 I need to do something like this</p> <pre><code>SELECT * FROM tblsites WHERE ID IN(SELECT IF(DateRequired >= '2013-01-01 00:00:00' AND DateRequired <= '2013-01-31 23:59:59', SiteID, '' ) AS SiteID FROM `tblmovements` WHERE TicketStatus IN ( 0, 1 ) GROUP BY SiteID) AND LENGTH(SiteName)>0 ORDER BY SiteName ASC </code></pre> <p>Basically it should return me a list of site data where that site has had its first movement within the date range selected. The DateRequired column is my movement date in tblmovements and tblmovements stores the SiteID as a column. Ticket status you can ignore its just a flag to say the ticket is active it shouldn't have an outcome on the query.</p> <p>So my theory was if I select all the tickets within tblmovements where they are active and and group them on the siteid then I could have the sub query only return the site ID if the DateRequired was between my input dates which would then allow me to do a simple SELECT WHERE IN() on the sites tables. The LENGTH clause is simply because the if returns a zero length on the else clause so rather than filter it out in script I'd like to filter them out on query.</p> <p>The query runs with no errors but is absolubtly slow as hell (30 minutes + and still running) I can't for the life of me think of a better way of writing it its probably just Friday morning blues but any suggestions to wake me back up would be much appreciated.<br> A better way of writing it completely would also be fine if you can make sense of my rambling above.</p> <p><strong>EDIT:</strong> Dummy Tables.</p> <pre><code>-- -- Table structure for table `tblmovements` -- CREATE TABLE IF NOT EXISTS `tblmovements` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `SiteID` bigint(20) unsigned NOT NULL, `TicketStatus` smallint(3) NOT NULL DEFAULT '1', `DateRequired` datetime DEFAULT NULL, PRIMARY KEY (`ID`), KEY `SiteID` (`SiteID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `tblmovements` -- INSERT INTO `tblmovements` (`ID`, `SiteID`, `TicketStatus`, `DateRequired`) VALUES (1, 1, 1, '2013-01-02 00:00:00'), (2, 1, 1, '2013-01-02 00:00:00'), (3, 1, 1, '2013-02-02 00:00:00'), (4, 1, 1, '2013-02-02 00:00:00'), (5, 1, 1, '2013-02-02 00:00:00'), (6, 2, 1, '2012-02-02 00:00:00'), (7, 2, 1, '2012-02-02 00:00:00'), (8, 2, 1, '2012-01-20 00:00:00'), (9, 2, 1, '2013-01-02 00:00:00'), (10, 2, 1, '2013-01-02 00:00:00'); -- -------------------------------------------------------- -- -- Table structure for table `tblsites` -- CREATE TABLE IF NOT EXISTS `tblsites` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `SiteName` varchar(100) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `tblsites` -- INSERT INTO `tblsites` (`ID`, `SiteName`) VALUES (1, 'Site 1'), (2, 'Site 2'); </code></pre> <p><strong>EDIT 2:</strong></p> <p>OK on steves suggestion I've changed the query to this</p> <pre><code>SELECT tblmovements.SiteID, tblsites.SiteName FROM tblmovements INNER JOIN tblsites ON tblmovements.SiteID = tblsites.ID WHERE (tblmovements.TicketStatus = 0 OR tblmovements.TicketStatus = 1) AND tblmovements.DateRequired>='2013-01-01 00:00:00' AND tblmovements.DateRequired<='2013-01-31 23:59:59' GROUP BY tblmovements.SiteID </code></pre> <p>Which is now semi-working. The problem I have now though is that this is only selecting if there is a ticket between that date range. What I now need to do is only return the result set IF the entry in the tblmovements is the first occurance of that site ID within that table. If you go back to the primary query you'll notice there's an IF on the sub select.</p> <p><strong>EDIT 3</strong></p> <p>Right I think I've cracked it based off Steves input and sudden flash of non-Friday morning moron.</p> <pre><code>SELECT tblmovements.SiteID, tblsites.SiteName FROM tblmovements INNER JOIN tblsites ON tblmovements.SiteID = tblsites.ID WHERE (tblmovements.TicketStatus = 0 OR tblmovements.TicketStatus = 1) AND tblmovements.DateRequired>='2013-01-01 00:00:00' AND tblmovements.DateRequired<='2013-01-31 23:59:59' AND (SELECT COUNT(*) FROM tblmovements t3 WHERE t3.DateRequired<'2013-01-01 00:00:00' AND t3.SiteID=tblmovements.SiteID)<=0 GROUP BY tblmovements.SiteID </code></pre> <p>Added an additional conditional to just give me a count of tickets before the start date so in theory if it returns a result set where there's tickets between my required dates and there's no tickets before the start date then all the siteid's returned should be the ones I require.</p> <p>Testing so far is returning the data sets I am expecting which is good and its noticably quicker <strong>Query Execution Time 0.5711 sec</strong> bonus.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload