mysql慢查询表格_Mysql +大表格=慢查询？

我在Mysql的一个大表中遇到了一些性能问题：

该表有3800万行，大小为3GB。

我想通过测试2列进行选择：

我尝试了很多索引(每列有一个索引，2列有一个索引)，但我仍然有很慢的查询：如下所示，超过4秒可以获取1644行：

SELECT `twstats_twwordstrend`.`id`, `twstats_twwordstrend`.`created`, `twstats_twwordstrend`.`freq`, `twstats_twwordstrend`.`word_id` FROM `twstats_twwordstrend` WHERE (`twstats_twwordstrend`.`word_id` = 1001 AND `twstats_twwordstrend`.`created` > '2011-11-07 14:01:34' );

...

...

...

1644 rows in set (4.66 sec)

EXPLAIN SELECT `twstats_twwordstrend`.`id`, `twstats_twwordstrend`.`created`, `twstats_twwordstrend`.`freq`, `twstats_twwordstrend`.`word_id` FROM `twstats_twwordstrend` WHERE (`twstats_twwordstrend`.`word_id` = 1001 AND `twstats_twwordstrend`.`created` > '2011-11-07 14:01:34' );

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

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

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

| 1 | SIMPLE | twstats_twwordstrend | range | twstats_twwordstrend_4b95d890,word_id_created_index | word_id_created_index | 12 | NULL | 1643 | Using where |

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

1 row in set (0.00 sec)

mysql> describe twstats_twwordstrend;

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

| Field | Type | Null | Key | Default | Extra |

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

| id | int(11) | NO | PRI | NULL | auto_increment |

| created | datetime | NO | | NULL | |

| freq | double | NO | | NULL | |

| word_id | int(11) | NO | MUL | NULL | |

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

4 rows in set (0.00 sec)

mysql> show index from twstats_twwordstrend;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| twstats_twwordstrend | 0 | PRIMARY | 1 | id | A | 38676897 | NULL | NULL | | BTREE | | |

| twstats_twwordstrend | 1 | twstats_twwordstrend_4b95d890 | 1 | word_id | A | 655540 | NULL | NULL | | BTREE | | |

| twstats_twwordstrend | 1 | word_id_created_index | 1 | word_id | A | 257845 | NULL | NULL | | BTREE | | |

| twstats_twwordstrend | 1 | word_id_created_index | 2 | created | A | 38676897 | NULL | NULL | | BTREE | | |

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

4 rows in set (0.03 sec)我还发现，在桌子上只取一行很慢，

mysql> SELECT `twstats_twwordstrend`.`id`, `twstats_twwordstrend`.`created`, `twstats_twwordstrend`.`freq`, `twstats_twwordstrend`.`word_id` FROM `twstats_twwordstrend` limit 10000000,1;

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

| id | created | freq | word_id |

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

| 10000001 | 2011-09-09 15:59:18 | 0.0013398539559188 | 41295 |

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

1 row in set (1.73 sec)...在桌子开始时不慢，

mysql> SELECT `twstats_twwordstrend`.`id`, `twstats_twwordstrend`.`created`, `twstats_twwordstrend`.`freq`, `twstats_twwordstrend`.`word_id` FROM `twstats_twwordstrend` limit 1,1;

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

| id | created | freq | word_id |

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

| 2 | 2011-06-16 10:59:06 | 0.00237777777777778 | 2 |

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

1 row in set (0.00 sec)该表使用Innodb引擎。我怎样才能加快查询大表？