Jump to content


Photo

Best way to retrieve aggregate summary info


  • Please log in to reply
7 replies to this topic

#1 mookiha

mookiha
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 17 September 2006 - 10:01 AM

I have 3 tables

mypoints <- could be thousands of records
id
user_id
cat_id
points
status

mycategories <- 10-20 records
id
description
acct_id
status

myaccounts <- a few records
id
description
status

Simple 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:
SELECT a.id, a.description, SUM( p.points ) AS currentbalance, SUM( IF( p.status = 0, 0, p.points ) ) AS availablebalance, COUNT( p.points ) AS records
FROM myaccounts AS a
LEFT JOIN mycategories AS c ON c.acct_id = a.id
LEFT JOIN mypoints AS p ON p.cat_id = c.id
WHERE a.status = 1
AND c.status = 1
AND p.user_id = $user_id
GROUP BY a.id
ORDER BY a.ordering
This returns all account summary info if a user has points

So 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.
SELECT a.id, a.description, SUM( p.points ) AS currentbalance, SUM( IF( p.status = 0, 0, p.points ) ) AS availablebalance, COUNT( p.points ) AS records
FROM mypoints AS p
LEFT JOIN mycategories AS c ON c.id = p.cat_id
LEFT JOIN myaccounts AS a ON a.id = c.acct_id
WHERE a.status = 1
AND c.status = 1
AND p.user_id = $user_id
GROUP BY a.id
ORDER BY a.ordering

Is it my understanding that the first query is better? Or maybe neither is and someone has a better way of doing this.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 September 2006 - 02:49 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 mookiha

mookiha
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 18 September 2006 - 07:18 AM

Thanks, I didn't think about the ordering.

Have any recommendations on MySQL books geared towards noobs?

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,015 posts

Posted 18 September 2006 - 12:18 PM

If you want all accounts, even if they have no points, you need

myaccounts LEFT JOIN mypoints

and not

mypoints LEFT JOIN myaccounts
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 September 2006 - 05:24 PM

Having never used a book, I can't recommend any.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 mookiha

mookiha
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 20 September 2006 - 01:25 AM

If you want all accounts, even if they have no points, you need

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
HAVING records > 0
to go back to accounts with points only

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,015 posts

Posted 20 September 2006 - 01:32 AM

myaccounts INNER JOIN mypoints

will give just those accounts with points
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 mookiha

mookiha
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 24 September 2006 - 10:44 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users