1

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
  • did it do anything?Whats the time now? – Mihai Jan 27 '14 at 13:02
  • Yes, it's quite an improvement. Now the query won't take much longer than 2 seconds in the worst case, which would have otherwise taken 15+ seconds. Thanks for your help! – Ian Jan 27 '14 at 18:06
1

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
  • The only change needed is the first line where you must disambiguate the accessid field name: select count(distinct a.accessid) as c. – Ian Jan 27 '14 at 18:08
  • 1
    I think you could further improve it with a composite index on acesslog(date,userid,is_robot,is_admin,url)-in this order – Mihai Jan 27 '14 at 18:38
  • Adding this index brings query time down to about 300ms. I can now run a week of data in ~3s. Thanks again Mihai! – Ian Jan 29 '14 at 8:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.