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.
Showing posts with label update speed. Show all posts
Showing posts with label update speed. Show all posts
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:
Comments (Atom)