Stuff

mysql> SHOW CREATE TABLE datetest;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                           |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| datetest | CREATE TABLE `datetest` (
  `id` int(11) NOT NULL auto_increment,
  `t` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=132186 DEFAULT CHARSET=utf8 | 
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM datetest WHERE t > '2006-09-24 18:19:12';
+----------+
| COUNT(*) |
+----------+
|   132184 | 
+----------+
1 row in set (0.12 sec)

mysql> SELECT COUNT(*) FROM datetest WHERE UNIX_TIMESTAMP(t) > UNIX_TIMESTAMP('2006-09-24 18:19:12');
+----------+
| COUNT(*) |
+----------+
|   132184 | 
+----------+
1 row in set (0.26 sec)

========================

4 million rows, no indexes
mysql> SELECT COUNT(*) FROM datetest WHERE t > '2006-09-24 18:19:12';
 SELECT COUNT(*) FROM datetest WHERE UNIX_TIMESTAMP(t) > UNIX_TIMESTAMP('2006-09-24 18:19:12');

+----------+
| COUNT(*) |
+----------+
|  4229919 | 
+----------+
1 row in set (5.76 sec)

mysql>  SELECT COUNT(*) FROM datetest WHERE UNIX_TIMESTAMP(t) > UNIX_TIMESTAMP('2006-09-24 18:19:12');
+----------+
| COUNT(*) |
+----------+
|  4229919 | 
+----------+
1 row in set (10.56 sec)

===================

now with an index
mysql> EXPLAIN SELECT COUNT(*) FROM datetest WHERE t > '2006-09-24 18:19:12';
+----+-------------+----------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
+----+-------------+----------+-------+---------------+------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | datetest | index | t             | t    | 4       | NULL | 4229920 | Using where; Using index | 
+----+-------------+----------+-------+---------------+------+---------+------+---------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN  SELECT COUNT(*) FROM datetest WHERE UNIX_TIMESTAMP(t) > UNIX_TIMESTAMP('2006-09-24 18:19:12');
+----+-------------+----------+-------+---------------+------+---------+------+---------+--------------------------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows    | Extra                    |
+----+-------------+----------+-------+---------------+------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | datetest | index | NULL          | t    | 4       | NULL | 4229920 | Using where; Using index | 
+----+-------------+----------+-------+---------------+------+---------+------+---------+--------------------------+