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.
Are there any good ways of going about updating a timestamp? My table is InnoDB and my database is MySQL.
Last Comment
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?
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.
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.
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.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Here's my table table structure
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:
Here's an example of an update query:
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.
# 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
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
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
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
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?
TomasHelgi: What does actuserix mean/do?
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
TomasHelgi: That command doesn't work. When I execute this:
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
CREATE actuserix ON users_last(
user_id ASC ,
lastactivity DESC
)
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
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
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
TomasHelgi: That actually slowed down my site, was I suppose to remove the current user_id and lastactivity indexes?
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
PHP
PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.
125K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
