OK Dokey...
Well, my first reaction is, "Hey, these are two totally different queries!?" :)
So, of course you're going to get different EXPLAIN results.
I can't imagine how the first query would be slower than the second, considering it's doing less work... but in either case, based on your EXPLAIN output, I'd recommend putting an index on TrackingActions (Action, UserID). The EXPLAIN's possible_keys column output shows NULL, which is a *BAD* sign, and especially telling that you don't have any primary keys on the table.
After that, you can get rid of the subqueries/derived tables from both examples entirely, and just do self joins, which will filter through the TrackingActions table:
The following will complete the request "Show me the number of distinct users who have viewed something, requested a download, downloaded, and confirmed the download":
SELECT COUNT(DISTINCT sa.UserID)
FROM TrackingActions sa
INNER JOIN TrackingActions ca
ON sa.UserID = ca.UserID
AND ca.Action='Confirm Download'
INNER JOIN TrackingActions ra
ON ca.UserID = ra.UserID
AND ra.Action='Request Download'
INNER JOIN TrackingActions va
ON ra.UserID = va.UserID
AND va.Action LIKE 'View%'
WHERE sa.Action = 'Download';
No parentheses needed. This query should be lightning fast with an index as described above. You should see an EXPLAIN output doing 'const, ref' lookups from one copy of the TrackingActions table to the next, with 'Using where, Using index' on all rows in the EXPLAIN output. If you don't post back here with the output.
Cheers,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com