[SOLVED] retrieving users not logged in within X days (not as easy as i thought)

[gnznroses]

i have a table like this:

user_id

site_id

logged_on

 

it logs the IPs and dates of users' activity.

(i then have another table with all of the user account info)

 

i need users who haven't logged on in the past 90 days.

first i tried looping through all users, checking their last login date (using DISTINCT, ordering by logged_on DESC, and using LIMIT 1)

that was way too slow, because that loops through ALL users, even recent ones. it became obvious i'll have to work with the IP logs directly, and determine the correct users from that data.

 

so i then came up with this:

 

SELECT DISTINCT user_id, site_id, logged_on

FROM ip_logs

WHERE logged_on<='$datelimit'

GROUP BY user_id

ORDER BY logged_on DESC";

 

(where date limit is the date 90 days ago)

 

i soon realized that that also returns just about everyone, because anyone who registered more than 90 days ago has an entry that matches that. it doesn't check for the LATEST logged_on date for each user, just ANY date before the date limit.

 

is there a way to do this? i'm not the best at advanced mysql stuff (this is my first time even using GROUP BY).

 

(oh, and i know it'd be best to have a last_login field in the user table itself, but it's not my script/site and it can't be modified...)

[fenway]

Try this (untested):

 

SELECT t1.user_id, t1.site_id, t1.logged_on
FROM ip_logs AS t1
INNER JOIN 
( SELECT user_id, MAX(logged_on) AS latest_logon
FROM ip_logs
GROUP BY user_id ) AS t2 ON ( t1.user_uid = t2.user_uid AND t1.logged_on = t2.latest_logon )
WHERE t1.logged_on<='$datelimit'
ORDER BY t1.logged_on DESC

 

[gnznroses]

hmm, i let the query run for several minutes, but i stopped it after a while because it was still executing.

the ip_logs table has bout 165,000 entries. (there's about 5500 members).

 

is this doable at all, in a reasonable amount of time?

[fenway]

Post the SHOW CREATE TABLE for the ip_logs table.

[Barand]

SELECT  user_id, MAX(logged_on) as lastlog

FROM ip_logs

GROUP BY user_id

HAVING lastlog < CURDATE()-INTERVAL 90 DAY

ORDER BY lastlog DESC

[fenway]

Wow... brain cramp... that's much cleaner, Barand... hehe.

[Barand]

Just waiting to hear if it runs in under a fortnight

[gnznroses]

SELECT  user_id, MAX(logged_on) as lastlog

FROM ip_logs

GROUP BY user_id

HAVING lastlog < CURDATE()-INTERVAL 90 DAY

ORDER BY lastlog DESC

 

that seems to have done it

and it executes in .4 seconds 

i'll test it more tonight and mark this resolved if it's definitely right (pretty sure it is).

thanks a lot to everyone who contributed  

[fenway]

SELECT  user_id, MAX(logged_on) as lastlog

FROM ip_logs

GROUP BY user_id

HAVING lastlog < CURDATE()-INTERVAL 90 DAY

ORDER BY lastlog DESC

 

that seems to have done it

and it executes in .4 seconds 

i'll test it more tonight and mark this resolved if it's definitely right (pretty sure it is).

thanks a lot to everyone who contributed 

I'd still be interested in seeing the underlying table structure/indexes... though 0.4s implies that it's indexed correctly ;-) Or maybe just the EXPLAIN, to see how cramped my brain actually was...

[gnznroses]

SELECT  user_id, MAX(logged_on) as lastlog

FROM ip_logs

GROUP BY user_id

HAVING lastlog < CURDATE()-INTERVAL 90 DAY

ORDER BY lastlog DESC

 

that seems to have done it

and it executes in .4 seconds 

i'll test it more tonight and mark this resolved if it's definitely right (pretty sure it is).

thanks a lot to everyone who contributed 

I'd still be interested in seeing the underlying table structure/indexes... though 0.4s implies that it's indexed correctly ;-) Or maybe just the EXPLAIN, to see how cramped my brain actually was...

 

i don't know a lot about SQL. this is what the explain link gives me:

 

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra 

1 SIMPLE fsr_ip_logs ALL NULL NULL NULL NULL 200453 

 

structure is:

Field Type Collation Attributes Null Default Extra Action

  id int(10)  UNSIGNED No  auto_increment             

  user_id int(10)  UNSIGNED No 0               

  site_id int(10)  UNSIGNED No 0               

  ip varchar(15) latin1_swedish_ci  No               

  logged_on datetime  No 0000-00-00 00:00:00               

 

Indexes:

Keyname Type Cardinality Action Field

PRIMARY  PRIMARY 200453      id 

 

i have no idea what most of this is... or how HAVING differs from WHERE. or how to make sense of your attempt. i'm getting an SQL book to try and learn.

[Barand]

Would index on user_id speed it up?

[gnznroses]

i know that in the next version of the script, an index is being added to logged_on.

but i don't know how indexes work...

[fenway]

Really? no index? No wonder my version was slow. 

 

It has nothing to do with a "version of a script" -- add the index, and voila! It's faster.

[gnznroses]

haha, i added an index and now that sql query takes .57 seconds (Barand's)