Re: COUNT(*) on innodb

[Jay Pipes]

You'd think, but because of the way MySQL does date arithmetic, it views the difference as 0 because they are in the same day. Instead, use TIMEDIFF():

mysql> SELECT @1:=NOW();
+---------------------+
| @1:=NOW() |
+---------------------+
| 2005-11-24 14:18:50 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT @2:=NOW();
+---------------------+
| @2:=NOW() |
+---------------------+
| 2005-11-24 14:18:53 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT TIMEDIFF(@2,@1);
+-----------------+
| TIMEDIFF(@2,@1) |
+-----------------+
| 00:00:03 |
+-----------------+
1 row in set (0.00 sec)

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com