第一次用mysql数据库，优化sql语句： SELECT COUNT(id) AS id FROM robots_article WHERE DATE_FORMAT(lastupdated,'%Y-%m-%d') = '2012-03-26'

查看执行计划发觉：该表的执行计划是all也就是全表扫描，由于robots_article是个大表，怀疑是索引关键字转换导致的。

mysql> explain SELECT COUNT(id) AS id FROM robots_article WHERE DATE_FORMAT(lastupdated,'%Y-%m-%d') = '2012-03-26';

+----+-------------+----------------+------+---------------+------+---------+------+---------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------------+------+---------------+------+---------+------+---------+-------------+

| 1 | SIMPLE | robots_article | ALL | NULL | NULL | NULL | NULL | 3465570 | Using where |

+----+-------------+----------------+------+---------------+------+---------+------+---------+-------------+

1 row in set (0.00 sec)

不过mysql中没有函数索引，看来只能用索引全扫描来完成了。

mysql> create index index_lastupdated on robots_article(lastupdated);

Query OK, 2738312 rows affected (21 min 54.30 sec)

Records: 2738312 Duplicates: 0 Warnings: 0

接下来查看执行计划index是用的全扫描扫描，问题解决，不过该sql应该用索引范围rang是可以达到最优的效果，不过暂时还没有想到如何用mysql来实现oracle的函数索引功能。

mysql> explain SELECT COUNT(id) AS id FROM robots_article WHERE DATE_FORMAT(lastupdated,'%Y-%m-%d') = '2012-03-26'

-> ;

+----+-------------+----------------+-------+---------------+-------------------+---------+------+---------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------------+-------+---------------+-------------------+---------+------+---------+--------------------------+

| 1 | SIMPLE | robots_article | index | NULL | index_lastupdated | 5 | NULL | 3417754 | Using where; Using index |

+----+-------------+----------------+-------+---------------+-------------------+---------+------+---------+--------------------------+

1 row in set (0.00 sec)