MySql JOIN on Date takes too long
I have two very simple queries that I'm running, as shown below. They both contain the date and one piece of info I need. I want to get those back as a single table on my query. Both queries take around .03 seconds to run. Whenever I've tried to JOIN them though, they take forever. Can somebody please help me with this syntax?
SELECT date(date) AS Day, sum(total) / 3600.0 AS Hours FROM events.Site_Total WHERE date > '2012-01-01' GROUP BY Day; SELECT date(sdate) AS Day, sum(arg) AS Count FROM events.uptimer WHERE state > '2012-01-01' GROUP BY Day;
I know this is simple, but the proper JOIN syntax that won't take forever to run is eluding me. Note that there are multiple entires per day with different times, which is why I was doing the date(date) thing and grouping by that.
Hope this will work ::
Select site.Day, site.Hours, uptimer.Day, uptimer.Count from (SELECT date(date) AS Day, sum(total) / 3600.0 AS Hours FROM events.Site_Total WHERE DATE(date) > '2012-01-01' GROUP BY Day) as site inner join ( SELECT date(sdate) AS Day, sum(arg) AS Count FROM events.uptimer WHERE DATE(sdate)> '2012-01-01' GROUP BY Day) as uptimer on (site.Day=uptimer.Day)
At the very least, you'll want to make sure there's an index added to each table, on the columns that you're joining on. So if you're joining on the date column, make sure each table has an index on date.
A second thing to consider is whether the indices are what's called covering for your queries. That means, the indices contain (as included columns) the columns needed to complete the query. In the ones you listed, these columns would be total for the "Site_Total" table, and arg and state for the "events.uptimer" table.