1

I am needing some SQL help. I have a SELECT statement that references several tables and is hanging up in the MySQL database. I would like to know if there is a better way to write this statement so that it runs efficiently and does not hang up the DB? Any help/direction would be appreciated. Thanks.

Here is the code:

Select Max(b.BurID) As BurID 
From My.AppTable a, 
My.AddressTable c, 
My.BurTable b 
Where a.AppID = c.AppID 
And c.AppID = b.AppID 
And (a.Forename = 'Bugs' 
And a.Surname = 'Bunny' 
And a.DOB = '1936-01-16' 
And c.PostcodeAnywhereBuildingNumber = '999' 
And c.PostcodeAnywherePostcode = 'SK99 9Q9' 
And c.isPrimary = 1 
And b.ErrorInd <> 1  
And DateDiff(CurDate(), a.ApplicationDate) <= 30)

There is NO mysql error in the log. Sorry.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Melinda
  • 1,501
  • 5
  • 25
  • 58
  • 2
    Make sure you have indexes on all the column used in the join. Also, while it has no effect on performance, try to write explicit joins instead of implicit joins. – Barmar Jun 11 '14 at 19:24
  • 1
    You could make your date-dependent condition [sargable](http://en.wikipedia.org/wiki/Sargable). – Andrew Morton Jun 11 '14 at 19:28
  • 1
    I find it hard to believe that this query would take forever. There is probably something else going on. Perhaps the table is locked? Check if there are any other open connections. – wvdz Jun 11 '14 at 19:32

1 Answers1

3

Pro tip: use explicit JOINs rather than a comma-separated list of tables. It's easier to see the logic you're using to JOIN that way. Rewriting your query to do that gives us this.

select Max(b.BurID) As BurID 
  From My.AppTable AS a 
  JOIN My.AddressTable AS c ON a.AppID = c.AppID 
  JOIN My.BurTable  AS b ON c.AppID = b.AppID
 WHERE (a.Forename = 'Bugs' 
   And a.Surname = 'Bunny' 
   And a.DOB = '1936-01-16' 
   And c.PostcodeAnywhereBuildingNumber = '999' 
   And c.PostcodeAnywherePostcode = 'SK99 9Q9' 
   And c.isPrimary = 1 
   And b.ErrorInd <> 1  
   And DateDiff(CurDate(), a.ApplicationDate) <= 30)

Next pro tip: Don't use functions (like DateDiff()) in WHERE clauses, because they defeat using indexes to search. That means you should change the last line of your query to 

  AND a.ApplicationDate >= CurDate() - INTERVAL 30 DAY

This has the same logic as in your query, but it leaves a naked (and therefore index-searchable) column name in the search expression.

Next, we need to look at your columns to see how you are searching, and cook up appropriate indexes.

Let's start with AppTable. You're screening by specific values of Forename, Surname, and DOB. You're screening by a range of ApplicationDate values. Finally you need AppID to manage your join. So, this compound index should help. Its columns are in the correct order to use a range scan to satisfy your query, and contains the needed results.

 CREATE INDEX search1 USING BTREE 
     ON AppTable 
        (Forename, Surname, DOB, ApplicationDate, AppID)

Next, we can look at your AddressTable. Similar logic applies. You'll enter this table via the JOINed AppID, and then screen by specific values of three columns. So, try this index

 CREATE INDEX search2 USING BTREE 
     ON AddressTable 
        (AppID, PostcodeAnywherePostcode, PostcodeAnywhereBuildingNumber, isPrimary)

Finally, we're on to your BurTable. Use similar logic as the other two, and try this index.

 CREATE INDEX search3 USING BTREE 
     ON BurTable 
        (AppID, ErrorInd, BurID)

This kind of index is called a compound covering index, and can vastly speed up the sort of summary query you have asked about.

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thanks Ollie. I appreciate the comprehensive reply. Great info. Though when I put in your suggestion for the DATEDIFF function I did not get my expected results. – Melinda Jun 11 '14 at 20:14
  • What are your expected results? Did I get the logic right? I often misread `DATEDIFF()` functions. ApplicationDate more than thirty days ago? – O. Jones Jun 11 '14 at 20:23
  • Good on you for pointing out the use of the SQL antipattern of implicit joins. – HLGEM Jun 11 '14 at 20:33
  • Hey Ollie, when I execute the statement with the DATEDIFF() I get 14 records. When I insert your suggestion, I get 0 records. Those were my expected results. ApplicationDate within the last 30 days. Thanks again. – Melinda Jun 12 '14 at 12:07
  • Darn. I read the Datediff wrong. I do that a lot. I'll edit my answer to flip the `<=` to `>=`. Just from curiosity, how much performance improvement did you see? – O. Jones Jun 12 '14 at 12:12