mysql下建立索引讓其index全掃描

第一次用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)