I'm trying to calculate the number of visitors to the homepage of my site who did NOT then log in to the member's section of the site, while also filtering bots and admin users. Bots and admins are marked as such in the table by a separate script.
The way I'm currently doing it is counting all distinct sessions (
accessid) without a logged in user (
userid = 0), then counting all the distinct sessions with a logged in user (
userid > 0), and finally excluding the logged in sessions from the non-logged in sessions and counting that result.
Here's the query that does the above:
SELECT Count(DISTINCT accessid) AS c FROM accesslog WHERE date = '2014-01-21' AND userid = 0 AND is_robot = 0 AND is_admin = 0 AND url = 'www.example.com/' AND accessid NOT IN (SELECT DISTINCT( accessid ) FROM accesslog WHERE DATE = '2014-01-21' AND userid > 0 AND is_robot = 0 AND is_admin = 0) ORDER BY c DESC
This query can take from 1-7 seconds depending on the traffic on a given day. Is there any way I can speed up this query?
explain output for the above query:
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY accesslog ref date,userid,dateaff,url,dateaccessid,userid+accessid,is_robot,is_admin date 3 const 6113 100.00 Using where; Using temporary 2 DEPENDENT SUBQUERY accesslog index_subquery accessid,date,userid,dateaff,dateaccessid,userid+accessid,is_robot,is_admin accessid 32 func 2 100.00 Using where