Search code examples
mysqltimeexecution

MySQL Query takes time to execute

The queries are as simple as that:

Eg 1: "UPDATE threads SET hits = hits+1 WHERE id = TID"
Eg 2: "UPDATE users SET last_activity = unix_timestamp() WHERE id = UID"

`users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `last_activity` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

`threads` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `hits` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Let's take this query as example: 

mysql_query("UPDATE users SET last_activity = unix_timestamp() WHERE id = UID");

without the query, script takes ~30 ms to execute:

enter image description here

with the query, script takes ~110 ms to execute:

enter image description here

Best regards, Duluman Edi

LE: The code that generates the execution time is the following:

///this is placed above any code
$mtime = explode(' ',microtime()); 
$mtime = $mtime[1] + $mtime[0]; 
$tstart = $mtime;
.....
my game code is here
.....
$mtime = explode(' ',microtime());
echo lang('info','page_load').' '.number_format(($mtime[1] + $mtime[0]) -    $tstart,4,'.','')*1000;

This is how I calculate the execution time, and it's pretty accurate, never failed me. P.S. - The real execution time is about 15 ms lower, because I'm hosting this on Windows at the moment.

Solution

  • I guess I could make an ajax call on $(document).ready() to make this changes after page loads, because this UPDATE statement is quite nested up.