mookiha Posted September 17, 2006 Share Posted September 17, 2006 I have 3 tablesmypoints <- could be thousands of records id user_id cat_id points statusmycategories <- 10-20 records id description acct_id statusmyaccounts <- a few records id description statusSimple structure of myaccounts which contains mycategories which contains mypoints.For one particular user I would like to retrieve the summary information of points, I have a working sql query but would like to know if it is the best way of doing it. I have little understanding of joins and such.Here is what I have so far:[code]SELECT a.id, a.description, SUM( p.points ) AS currentbalance, SUM( IF( p.status = 0, 0, p.points ) ) AS availablebalance, COUNT( p.points ) AS recordsFROM myaccounts AS aLEFT JOIN mycategories AS c ON c.acct_id = a.idLEFT JOIN mypoints AS p ON p.cat_id = c.idWHERE a.status = 1AND c.status = 1AND p.user_id = $user_idGROUP BY a.idORDER BY a.ordering[/code]This returns all account summary info if a user has pointsSo then I got to experimenting, the following works also but because of my lack of understanding the internal workings of mysql I was wondering which is better for the sql engine.[code]SELECT a.id, a.description, SUM( p.points ) AS currentbalance, SUM( IF( p.status = 0, 0, p.points ) ) AS availablebalance, COUNT( p.points ) AS recordsFROM mypoints AS pLEFT JOIN mycategories AS c ON c.id = p.cat_idLEFT JOIN myaccounts AS a ON a.id = c.acct_idWHERE a.status = 1AND c.status = 1AND p.user_id = $user_idGROUP BY a.idORDER BY a.ordering[/code]Is it my understanding that the first query is better? Or maybe neither is and someone has a better way of doing this. Link to comment https://forums.phpfreaks.com/topic/21049-best-way-to-retrieve-aggregate-summary-info/ Share on other sites More sharing options...
fenway Posted September 17, 2006 Share Posted September 17, 2006 Good question -- usually, the join optimizer will handle this, but not with outer JOINs, because the order matters. I would go with the second one, because you're using ORDER BY/GROUP BY, which will incur a filesort if it's not on the "non-joined" table. Link to comment https://forums.phpfreaks.com/topic/21049-best-way-to-retrieve-aggregate-summary-info/#findComment-93508 Share on other sites More sharing options...
mookiha Posted September 18, 2006 Author Share Posted September 18, 2006 Thanks, I didn't think about the ordering.Have any recommendations on MySQL books geared towards noobs? Link to comment https://forums.phpfreaks.com/topic/21049-best-way-to-retrieve-aggregate-summary-info/#findComment-93842 Share on other sites More sharing options...
Barand Posted September 18, 2006 Share Posted September 18, 2006 If you want all accounts, even if they have no points, you needmyaccounts LEFT JOIN mypointsand notmypoints LEFT JOIN myaccounts Link to comment https://forums.phpfreaks.com/topic/21049-best-way-to-retrieve-aggregate-summary-info/#findComment-93898 Share on other sites More sharing options...
fenway Posted September 18, 2006 Share Posted September 18, 2006 Having never used a book, I can't recommend any. Link to comment https://forums.phpfreaks.com/topic/21049-best-way-to-retrieve-aggregate-summary-info/#findComment-94069 Share on other sites More sharing options...
mookiha Posted September 20, 2006 Author Share Posted September 20, 2006 [quote author=Barand link=topic=108377.msg436450#msg436450 date=1158581908]If you want all accounts, even if they have no points, you need[/quote]Thanks, I didnt fully test both queries yet, now I understand a little more the effects of the two.Then I could slip in a[code]HAVING records > 0[/code]to go back to accounts with points only Link to comment https://forums.phpfreaks.com/topic/21049-best-way-to-retrieve-aggregate-summary-info/#findComment-95016 Share on other sites More sharing options...
Barand Posted September 20, 2006 Share Posted September 20, 2006 myaccounts INNER JOIN mypointswill give just those accounts with points Link to comment https://forums.phpfreaks.com/topic/21049-best-way-to-retrieve-aggregate-summary-info/#findComment-95022 Share on other sites More sharing options...
mookiha Posted September 24, 2006 Author Share Posted September 24, 2006 That would make more sense. Using HAVING seems like it would have to go through the list twice.Thanks, you folks have been very helpful. Link to comment https://forums.phpfreaks.com/topic/21049-best-way-to-retrieve-aggregate-summary-info/#findComment-97599 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.