SQL Social - Online Users

Question

I've got a table

CREATE TABLE `tbl_users` (
  `user_id` int(11) NOT NULL auto_increment,
  `user_username` tinytext NOT NULL,
  `user_password` tinytext NOT NULL,
  `user_email` tinytext NOT NULL,
  `user_enabled` tinyint(4) NOT NULL default '1',
  `user_verified` tinyint(4) NOT NULL default '0',
  `user_verified_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `user_signup_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `user_login_date` datetime default NULL,
  `user_status` mediumtext NOT NULL,
  `user_online` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`user_id`)
)

Every time a user visits the website user_login_date updates and user_online is set to 1, which means he's online.

What query can I send to switch user_online to 0 (offline) if user's last visit was 10 minutes ago?

I've already done like that

UPDATE tbl_users SET user_online = '0' WHERE (NOW() - user_login_date) > INTERVAL 10 minute

But that didn't help.

Answer 1

You should do the calculations on the constants so that an index on user_login_date can be used:

UPDATE tbl_users
SET user_online = '0'
WHERE NOW() - INTERVAL 10 MINUTE > user_login_date