MySQL Forums
Forum List  »  Newbie

Re: Specifying multiple WHERE options within a group?
Posted by: Callum Macdonald
Date: July 01, 2005 05:52AM

== Join Query ==

mysql> EXPLAIN SELECT COUNT(DISTINCT ua.UserID)
-> FROM TrackingActions ua
-> INNER JOIN (SELECT UserID FROM TrackingActions
-> WHERE Action = 'Request Download') AS uad
-> ON ua.UserID = uad.UserID
-> INNER JOIN (
-> SELECT UserID FROM TrackingActions
-> WHERE Action = 'View Home'
-> ) AS uap
-> ON ua.UserID = uap.UserID;
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 83 | |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 435 | |
| 1 | PRIMARY | ua | ALL | NULL | NULL | NULL | NULL | 1150 | Using where |
| 3 | DERIVED | TrackingActions | ALL | NULL | NULL | NULL | NULL | 1150 | Using where |
| 2 | DERIVED | TrackingActions | ALL | NULL | NULL | NULL | NULL | 1150 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+
5 rows in set (0.00 sec)

== Sub, sub, sub Query! ==

mysql> EXPLAIN SELECT COUNT(*) FROM (SELECT * FROM TrackingActions AS ServeUsers,
-> (SELECT * FROM TrackingActions AS ConfirmUsers,
-> (SELECT * FROM TrackingActions AS RequestUsers,
-> (SELECT * FROM TrackingActions
-> WHERE Action LIKE "View%" GROUP BY TrackingActions.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;
+----+-------------+-----------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 43 | Using temporary; Using filesort |
| 2 | DERIVED | ServeUsers | ALL | NULL | NULL | NULL | NULL | 1150 | Using where |
| 3 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 68 | Using temporary; Using filesort |
| 3 | DERIVED | ConfirmUsers | ALL | NULL | NULL | NULL | NULL | 1150 | Using where |
| 4 | DERIVED | <derived5> | ALL | NULL | NULL | NULL | NULL | 310 | Using temporary; Using filesort |
| 4 | DERIVED | RequestUsers | ALL | NULL | NULL | NULL | NULL | 1150 | Using where |
| 5 | DERIVED | TrackingActions | ALL | NULL | NULL | NULL | NULL | 1150 | Using where; Using temporary; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+------+------+----------------------------------------------+
8 rows in set (0.38 sec)

Thanks Jay, you're a star - C.

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.