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.
Subscribe to:
Posts (Atom)