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.
davideo7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Terry WoodsIT GuruCommented:
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?
0
Dave BaldwinFixer of ProblemsCommented:
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.
0
ArgentiCommented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Ray PaseurCommented:
Is the column defined TIMESTAMP?  If so, it will be updated automatically when the row is updated (pay careful attention to the rules for UPDATE).  In other words, you would have no real reason to add the column to any INSERT or UPDATE query.

As far as slow queries go, have you used EXPLAIN?  See also this very good article.
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html
0
Tomas Helgi JohannssonCommented:
Hi!

You will need to have an index like this on your table
create actuserix on users (userid asc, last_activity desc )

This way you have always the newest users activity on the top giving the fastest query time for new users activity.

Having only one column on one or many indexes in a large table where you query with where clause with more than one columns is a bad idea. Use indexes that match the where clause of your queries to maximize your insert/update/delete as well as query performance.

Regards,
    Tomas Helgi
0
davideo7Author Commented:
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.
0
davideo7Author Commented:
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?
0
Tomas Helgi JohannssonCommented:
Hi!

The index will sertainly speed up queries like

select u.*
from users_last u
where user_id = <some id>
and lastactivity = <some id>

or

select u.*
from users_last u
where user_id = <some id>
and lastactivity between <some id> and <some other id>

or

select u.*
from users_last u
where user_id = <some id>
and lastactivity = (select max(uu.lastactivity) from users_last uu where uu.user_id = u.user_id)

These queries above will have to use 2 indexes with your current index setup (pk and lastactivity) but if my index suggestion is used then these queries will use only that index.
For large tables and indexes you will see large decrease in the speed of the queries as well as better access path in the explain. :)

Running queries on one index (index scan) is way better than running on 2 or more indexes and of course a table scan of large tables. Not to mention tables that are more than several millions of records and large tables in a simple to complex joins.  

Regards,
    Tomas Helgi
0
davideo7Author Commented:
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
0
Tomas Helgi JohannssonCommented:
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
0
davideo7Author Commented:
TomasHelgi: That actually slowed down my site, was I suppose to remove the current user_id and lastactivity indexes?
0
Terry WoodsIT GuruCommented:
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.
0
Tomas Helgi JohannssonCommented:
Hi!

Yes, you should remove other similar indexes as long as they don't play a role like unique indexes or primary keys or else you risk your data integrity.
You could also try to switch from DESC to ASC for lastactivity

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

And see how that helps.
All unnecessary  indexes should be removed to make sure the queries will use the new access path. Also after applying new indexes it is always good idea to run statistics on the table and indexes to help the database optimizer to choose the best access path available.

Always run Analyze table and/or optimize table on the users_last after adding new indexes.
http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html
http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html

It's a good rule of thumb to run the above regularly on any table especially high traffic ( high update/insert/delete ) tables.

Regards,
    Tomas Helgi
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.