Search found 6 matches

by mcmurphy510
Tue Aug 04, 2009 11:59 am
Forum: Databases
Topic: MySQL: SELF JOIN, using dates as columns
Replies: 23
Views: 2958

Re: MySQL: SELF JOIN, using dates as columns

I think that peerReport.summary_date BETWEEN '2009-06-24' AND '2009-06-26' is a bit faster. However, HAVING COALESCE produces a bit cleaner results (it gets rid of rows with all zeroes as well as NULLs). I wonder if it would make sense to do both. The table will start to get quite large over time, a...
by mcmurphy510
Fri Jul 31, 2009 1:12 pm
Forum: Databases
Topic: MySQL: SELF JOIN, using dates as columns
Replies: 23
Views: 2958

Re: MySQL: SELF JOIN, using dates as columns

VladSun, I used HAVING and COALESCE as you suggested, like this: SELECT     peerReport.realm,     sum(IF(peerReport.summary_date BETWEEN '2009-07-24' AND '2009-07-29',peerReport.mou,0)) AS TOTAL,     sum(IF(peerReport.summary_date = '2009-07-29',peerReport.mou,NULL)) AS '2009-07-29',     sum(IF(peer...
by mcmurphy510
Fri Jul 31, 2009 2:22 am
Forum: Databases
Topic: MySQL: SELF JOIN, using dates as columns
Replies: 23
Views: 2958

Re: MySQL: SELF JOIN, using dates as columns

VladSun, Not only does it work... it's FAST!!! It did a whole 31 days worth of columns in under 1/10 of a second! I may need to go out to a quarter (90 days) of data, but given that speed, I doubt it will be a problem. I'll try HAVING and COALESCE to see about limiting the return tomorrow... but it'...
by mcmurphy510
Wed Jul 29, 2009 7:07 pm
Forum: Databases
Topic: MySQL: SELF JOIN, using dates as columns
Replies: 23
Views: 2958

Re: MySQL: SELF JOIN, using dates as columns

Since it runs fine on 1, 2 and 3 dates, here's the one with 4 that takes so long: EXPLAIN SELECT     p.realm,     a.mou AS '2009-06-24',     b.mou AS '2009-06-25',     c.mou AS '2009-06-26',     d.mou AS '2009-06-27' FROM peerReport p     LEFT JOIN peerReport a ON p.realm=a.realm AND a.summary_date ...
by mcmurphy510
Wed Jul 29, 2009 5:52 pm
Forum: Databases
Topic: MySQL: SELF JOIN, using dates as columns
Replies: 23
Views: 2958

Re: MySQL: SELF JOIN, using dates as columns

Thanks pytrin, The query works, however it runs very slow with more than three dates listed, even with the indexes. Should I put another index on there for 'summary_date' as well? Also, if there are no entries for a given realm across all the dates listed, it still shows that realm, but it shows NUL...
by mcmurphy510
Tue Jul 28, 2009 3:37 pm
Forum: Databases
Topic: MySQL: SELF JOIN, using dates as columns
Replies: 23
Views: 2958

MySQL: SELF JOIN, using dates as columns

I think what I'm looking for here is some sort of advanced (SELF?) JOIN, and it's currently beyond me. I'm hoping (at least) one of you can help. :) I have a data table with a few fields. id (primary key), summary_date, realm, mou and tab. For this query, the only fields I'm interested in are summar...