Wednesday, March 9, 2011

Question about MySQL Update speed

I originally posted this as a question, but Eric Wood helped me solve it in email, so I've added the solution below. The minimum time MySQL with InnoDB tables takes to do an update on a 3Ghz Core 2 Duo runing 64-bit Ubuntu 10.10 is somewhere around 0.06 seconds, though I wonder if hard drive speed could be the gating factor? My average time using Hibernate is 0.15 seconds. I think JDBC would approach 0.06 seconds. This was done using the mysql command line client. Any thoughts would be appreciated. Also timings vs. other databases if you have a sense.
mysql> CREATE TABLE `test` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `last_click_date` datetime DEFAULT NULL,
    ->   `is_active` bit(1) NOT NULL DEFAULT b'0' COMMENT 'True if user is still logged in.',
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `id` (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.21 sec)

mysql> insert into test (last_click_date, is_active) values ('2011-03-07 14:18:16', b'1');
Query OK, 1 row affected (0.11 sec)

mysql> insert into test (last_click_date, is_active) values ('2011-03-07 14:18:16', b'1');
Query OK, 1 row affected (0.11 sec)

mysql> insert into test (last_click_date, is_active) values ('2011-03-07 14:18:16', b'1');
Query OK, 1 row affected (0.06 sec)

mysql> update test set last_click_date = '2011-03-07 14:18:16' where id = 1;
Query OK, 0 rows affected (0.07 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update test set last_click_date = now() where id = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update test set is_active = b'0' where id = 1;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update test set is_active = b'0' where id = 1;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update test set is_active = b'0' where id = 1;
Query OK, 0 rows affected (0.10 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update test set last_click_date = 20110307141816 where id = 1;
Query OK, 0 rows affected (0.06 sec)
Rows matched: 1  Changed: 0  Warnings: 0
It turns out that if I change the table to use the MyISAM engine, all the updates take 0.00 seconds.