MySQL Forums
Forum List  »  Newbie

Re: Specifying multiple WHERE options within a group?
Posted by: Callum Macdonald
Date: June 30, 2005 01:43AM

Wow, a wee spot of reading on JOINS and it all makes sense! Thanks Jay.

Btw, I spotted the deliberate typos with aup.UserID instead of uap.UserID and aud! :)

Quick question on optimisation, the qeury takes 21.47 seconds to run, whereas my many, many subqueries query takes only 0.27 seconds to run.

SELECT COUNT(*) FROM (SELECT * FROM UserActions AS ServeUsers,
(SELECT * FROM UserActions AS ConfirmUsers,
(SELECT * FROM UserActions AS RequestUsers,
(SELECT * FROM UserActions
WHERE Action LIKE "View%" GROUP BY UserActions.UserID) AS Views
WHERE RequestUsers.Action = "Request Download" GROUP BY RequestUsers.UserID) AS Requests
WHERE ConfirmUsers.Action = "Confirm Download" GROUP BY ConfirmUsers.UserID) AS Confirms
WHERE ServeUsers.Action = "Download" GROUP BY ServeUsers.UserID) AS Serves

I'd have thought the JOIN option would be far more efficient. Any suggestions?

Thanks again,


Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.