Avatar of davideo7
davideo7Flag for United States of America asked on

Tips for indexing a field that keeps track of time

I have a site which has 400,000+ users and one of the fields of one of my users tables keeps track of the users last activity as a timestamp.  Every time a user does something on my site, that timestamp updates to keep track of their last activity, the problem is, the query that updates the lastactivity field can take a while, up to 4 or more seconds.

Are there any good ways of going about updating a timestamp?  My table is InnoDB and my database is MySQL.
MySQL ServerPHP

Avatar of undefined
Last Comment
Tomas Helgi Johannsson

8/22/2022 - Mon
Terry Woods

If the problem is a single query that is taking 4 seconds, then it sounds like it needs optimising. Can you post an example of the SQL and tell us which indexes exist on the tables involved?
Dave Baldwin

I agree with Terry.  It is likely that your access method for the update is not using an index and is having to scan the table to find the desired row.
Argenti

I suppose the delay comes from the action of FINDING your row that must be updated (among your 400,000+ users), not doing the actual update.

UPDATE table_name SET dateField = dateValue    <-- this one is piece of cake
WHERE user_id_field = :specific_user_id       <-- that part is more difficult

Check if you have your WHERE field (or fields) indexed.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Ray Paseur

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
davideo7

Here's my table table structure
 	# 	Name 					Type 			Collation 	Attributes 	Null 	Default 	Extra 	Action
	1 	user_id 				mediumint(8) 				UNSIGNED 	No 		None 		
	2 	lastreadannouncements 	bigint(12) 								No 		None 		
	3 	lastreadnotifications 	bigint(12) 								No 		None 		
	4 	lastposttime 			bigint(12) 					UNSIGNED 	No 		0 		
	5 	lastBrowser 			varchar(512) 	latin1_swedish_ci 		No 		None 		
	6 	lastactivity 			bigint(12) 					UNSIGNED 	No 		0 		
	7 	lastactivity_cometchat 	bigint(12) 								No 		None 		
	8 	lastip 					varchar(15) 	latin1_swedish_ci 		No 		0.0.0.0 		
	9 	lasturl 				varchar(100) 	latin1_swedish_ci 		Yes 	NULL 		
	10 	lastforum 				tinyint(3) 					UNSIGNED 	No 		0 		

Open in new window


I have 2 main users table, that that keeps track of name and personal info and one that keeps track of last activity sort of stuff; this one keeps track of last activity data.

Here's my indexes of that same table:
Keyname			Type	Unique	Packed	Column			Cardinality	Collation	Null	Comment
PRIMARY			BTREE	Yes		No		user_id			376222		A			No	
lastposttime	BTREE	No		No		lastposttime	11			A			No	
lastactivity	BTREE	No		No		lastactivity	376222		A			No	

Open in new window


Here's an example of an update query:
UPDATE users_last SET lastactivity=1377803413, lastip='0.0.0.0',lastforum=0,lasturl='/boards/newposts3.php',lastBrowser='Mozilla/5.0 (Windows NT 6.2; WOW64; rv:23.0) Gecko/20100101 Firefox/23.0' WHERE user_id=1

Open in new window



EDIT: The formatting of my table structure didn't turn out, just copy and paste it into something like Notepad++ if you want it to be easier to read.
ASKER
davideo7

Ray_Paseur: The field isn't timestamp, I use PHP to add in the current timestamp.  I have not used Explain but I'll look into it.

  TomasHelgi: What does actuserix mean/do?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
davideo7

TomasHelgi: That command doesn't work.  When I execute this:
CREATE actuserix ON users_last(
user_id ASC ,
lastactivity DESC
)

Open in new window


I get this error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'actuserix on users_last (user_id asc, lastactivity desc )' at line 1
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tomas Helgi Johannsson

Hi!

This should work

CREATE INDEX actuserix ON users_last(
user_id ASC ,
lastactivity DESC
);

See manual http://dev.mysql.com/doc/refman/5.0/en/create-index.html

Regards,
    Tomas Helgi
ASKER
davideo7

TomasHelgi: That actually slowed down my site, was I suppose to remove the current user_id and lastactivity indexes?
Terry Woods

An index on user_id and lastactivity (in that order) can be be used when querying which just a user_id but not when you only have a lastactivity value.

You'll need a separate index on lastactivity index too, if you do queries that don't use the user_id column.

You shouldn't need an index on just user_id any more though.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.