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.
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.
Labels:
MySQL,
update speed
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment