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.

Thanks!

Answers


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.


Need Your Help