Mysql – Counting number of visitors who did NOT log in

countMySQLsubquery

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?

Here's the 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

Best Answer

Do it with a LEFT JOIN

SELECT Count(DISTINCT accessid) AS c
FROM   accesslog a
LEFT JOIN
(SELECT accessid
 FROM   accesslog
 WHERE  DATE = '2014-01-21'
 AND userid > 0
 AND is_robot = 0
 AND is_admin = 0 
 GROUP BY accessid)x
ON a.accessid=x.accessid
WHERE  a.date = '2014-01-21'
   AND a.userid = 0
   AND a.is_robot = 0
   AND a.is_admin = 0
   AND a.url = 'www.example.com/'
   AND x.accessid IS NULL
Related Question