Thanks to all that are taking a look at this.

I'm trying to create a query that will get some information from a
main table as well as some aggregated from other tables. For example,
let's say I have three tables with some fields:
User: username, firstname, lastname
Purchase: username, date, amount, items
View: username, date, pageUrl

I want to create a query that will get the user's info (username and
first and last name) as well as the total purchase amounts and the
total page views. I have this as a query:

SELECT u.username, u.firstname, u.lastname, sum(p.amount) AS
totalAmount, count(v.pageUrl) AS countPages
FROM (User u LEFT JOIN Purchase p ON u.username=p.username) LEFT JOIN
View v ON u.username=v.username
GROUP BY u.username, u.firstname, u.lastname;

This query works, but takes forever (with ~2000 users, ~2 purchases/
user, ~10 views/user = ~20 minutes). Clearly this is not ideal.

Does someone have any idea on how I could create a better query to
handle this?

Thanks in advance, Andy

On Feb 15, 8:48 am, andrewfse... (AT) gmail (DOT) com wrote:
count(v.pageUrl). Because the interim result rows of joining 3 tables
will be multiply of rows of each table.
(In your example, 2000 x 2 x 10 = 40000 rows)
Grouping this interim joined results by username, sum(p.amount) will
be about 10 times larger than without joining View table and
count(v.pageUrl) will be about 2 times larger than without joining
Purchase table.

Although, I don't know what DBMS are you using,
I thought that it is worth to try following examples.
1)
SELECT u.username, u.firstname, u.lastname
, MAX(totalAmount) AS totalAmount
, MAX(countPages) AS countPages
FROM User u
LEFT JOIN
(SELECT username, sum(amount) AS totalAmount
FROM Purchase
GROUP BY username
) p
ON u.username=p.username
LEFT JOIN
(SELECT username, count(pageUrl) AS countPages
FROM View
GROUP BY username
) v
ON u.username=v.username
GROUP BY u.username, u.firstname, u.lastname;

2)
SELECT u.username, u.firstname, u.lastname
, MAX(totalAmount) AS totalAmount
, MAX(countPages) AS countPages
FROM User u
LEFT JOIN
TABLE
(SELECT sum(p.amount) AS totalAmount
FROM Purchase p
WHERE u.username=p.username
) p
ON 0=0
LEFT JOIN
TABLE
(SELECT count(v.pageUrl) AS countPages
FROM View v
WHERE u.username=v.username
) v
ON 0=0
GROUP BY u.username, u.firstname, u.lastname;

Correction:
will be multiply of average rows per distinct value of joined column
of each table.
(In your example, 2000 x 2 x 10 = 40000 rows)

On Feb 15, 8:43 am, "Tonkuma" <tonk... (AT) jp (DOT) ibm.com> wrote:
[...]
In addition to Tonkuma's suggestion, Is it possible to Purchase
something without viewing it? If not you should be possible to replace
1 outer join with an inner join. I.e. (not tested)

SELECT v.username, v.firstname, v.lastname, v.cnt,
coalesce(sum(p.amount), 0)
FROM (
select u.username, u.firstname, u.lastname, count(*) as cnt
from User u INNER JOIN View x ON u.username = x.username
GROUP BY u.username, u.firstname, u.lastname
) V (username, firstname, lastname, cnt)
LEFT OUTER JOIN Purchase p ON p.username = v.username
GROUP BY v.username, v.firstname, v.lastname

/Lennart

Hi all, and thanks for the responses (I tried to respond like a week
ago, but now looking at this, I realized it was never posted)...

Just for more clarification on what exactly I'm looking for - I would
in a sense like to get a list of users (with their information of
username and first and last names). On top of that, for each of the
users in the list of users, I would like to have the sum of their
purchases (there could have been no purchases for some users, so that
would be zero (0) ). I would also like to have the number of "views"
that the user has made (which could also be none).

Other info, I'm using MySql 4.0.

Thank you all in advance, Andy

Andy,
Do you have an index on username for all tables? Also have you
analyzed the tables?

You should run an EXPLAIN on the tables to determine how MySQL is
executing the query. Based on the table sizes you provide, this type
of query should take less than a second to execute on MySQL (based on
my past experience).
-Tri
http://architechsolutions.com

On Feb 28, 11:50 am, andrewfse... (AT) gmail (DOT) com wrote: