CODEWITHSUNDEEP
phpmysqltime
Niet the Dark Absol
Niet the Dark Absol

Reputation: 324730

Why is this query abnormally long?

I'm timing various part of the site's "initialisation" code (including such things as verifying the user is logged in, connecting to the database, importing functions...)

This query is currently taking up abouve half the total initialisation time all by itself:

$sql = "update `users` set `lastclick`=now(),".(substr($_SERVER['PHP_SELF'],0,6) == "/ajax/" ? "" : " `lastactive`=now(),")." `lastip`='".addslashes($_SERVER['REMOTE_ADDR'])."' where `id`=".$userdata['id'];

Generating the query takes no time at all, it's the running that's the problem. Example result query:

update `users` set `lastclick`=now(), `lastactive`=now(), `lastip`='192.168.0.1' where `id`=1

Simple enough query, right? I am the only user on the server right now, there is literally nothing else running. So why does a simple update take up more time than connecting to the database, SELECTing the user data in the first place, validating the cookies, and defining a bunch of functions all combined?

(I just tried replacing now() with a literal value, but that made no difference - in fact it ended up taking 13ms the first time instead of 4...)

EDIT: As requested:

explain select * from  `users` where `id`=1
1 row returned

id select_type table type  possible_keys key     key_len ref   rows Extra
1  SIMPLE      users const PRIMARY       PRIMARY 4       const 1

Upvotes: 0

Views: 94

Answers (1)

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324730

Solved my own mystery. Turns out one of the fields being updated (lastactive) was in an index, and the slowness was coming from rebuilding that index.

Since the only time that index might be used is in updating the list of users who are online, and that only happens by cron every set interval, I've dropped the index and now the query runs a heck of a lot faster.

Thanks to those who tried to help - you did help me find the problem, indirectly!

Upvotes: 1

Related Questions