EXPLAIN sql执行语句 分析一下为什么为这么慢mysql> SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600;+----------+| COUNT(*) |+----------+| 3971 |+----------+1 row in set (1.04 sec)mysql> EXPLAIN SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: Headline type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 302116 Extra: Using where1 row in set (0.00 sec) The NULL value in the key column of the EXPLAIN output tell us that MySQL won't be using an index for this query. In fact, the NULL value in the possible_keys column tells us that there were no indexes to pick from at all. If this type of query is likely to be common, we can simply add an index and rerun the query (or the EXPLAIN) to verify that MySQL uses it.
是这样的，我的数据库里有1400000记录，我把id号大于1380000的记录项的某一列值置为0 例如： update foo set bar=0 where id >=1380000这样一条语句需要花上50多秒 而我实际的程序中还需要联表查询... 比如说 update foo left join foo1 on foo.id = foo1.id set bar = 0 where bar = 1 and foo1.bar1=0 仿佛联表的时候并没有先考虑where中的条件，所以特别慢... 原来我自己写了一个程序，每1000条记录更新一次，但是这样就要先把记录取出来 执行的语句也要多很多...所以升级到4.1，想用嵌套查询来节省时间，但是实际效果并不为我想像得那么好 虽然小规模数据的时候看起来更快，但是到了大规模之后却仿佛更慢了 而且慢得不可忍受...
The NULL value in the key column of the EXPLAIN output tell us that MySQL won't be using an index for this query. In fact, the NULL value in the possible_keys column tells us that there were no indexes to pick from at all. If this type of query is likely to be common, we can simply add an index and rerun the query (or the EXPLAIN) to verify that MySQL uses it.
例如：
update foo set bar=0 where id >=1380000这样一条语句需要花上50多秒
而我实际的程序中还需要联表查询...
比如说
update foo left join foo1 on foo.id = foo1.id set bar = 0 where bar = 1 and foo1.bar1=0 仿佛联表的时候并没有先考虑where中的条件，所以特别慢...
原来我自己写了一个程序，每1000条记录更新一次，但是这样就要先把记录取出来
执行的语句也要多很多...所以升级到4.1，想用嵌套查询来节省时间，但是实际效果并不为我想像得那么好
虽然小规模数据的时候看起来更快，但是到了大规模之后却仿佛更慢了
而且慢得不可忍受...