mysql对于innodb的count(*) 以及count(1)测试比较

浏览次数:

数据表200多w记录

测试环境1(master db)

4核志强CPU
内存15G

  1. mysql> select count(*) from articles;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |  2871704 |
  6. +----------+
  7. 1 row in set (35.31 sec)

测试环境2(slave db)

AMD 双核
内存8G

  1. mysql> select count(1) from articles;
  2. +----------+
  3. | count(1) |
  4. +----------+
  5. |  2871704 |
  6. +----------+
  7. 1 row in set (2.71 sec)
  8.  
  9. mysql> select count(1) from articles;
  10. +----------+
  11. | count(1) |
  12. +----------+
  13. |  2871704 |
  14. +----------+
  15. 1 row in set (0.00 sec)

看来count(1)和count(*) 还是有区别的阿…

Submit Article :- BlinkList + Blogmarks + Digg + Del.icio.us + Ekstreme Socializer + Feedmarker + Furl + Google Bookmarks + ma.gnolia + Netvouz + RawSugar + Reddit + Scuttle + Shadows + Simpy + Spurl + Technorati + Unalog + Wink

Posted in: mysql

This entry was posted on 星期四, 十二月 24th, 2009 at 11:17 上午 and is filed under mysql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

5 Responses to “mysql对于innodb的count(*) 以及count(1)测试比较”

linvo 十二月 24th, 2009 at 12:56 下午

那myisam呢？

小黑 十二月 25th, 2009 at 9:18 上午

@LINVO
myisam 对于count(*) 似乎有优化,昨天请朋友测试了下 400w+的记录,两则执行时间相当,几乎没差

star 一月 16th, 2010 at 9:27 下午

黑米,学习了,呵呵!

Azure 六月 24th, 2010 at 4:47 下午

注意cache 其实两个效果差不多 都扫描全表

Azure 六月 24th, 2010 at 4:48 下午

我测是200多W的数据实际都在2-3秒左右 差不多啊

Leave a Reply