Re: Specifying multiple WHERE options within a group?

[Callum Macdonald]

== 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.