Mysql count performance

Question

select count (*) from mytable;
select count (table_id) from mytable; // table_id is the primary_key

both queries worked slowly in a table with 10 million rows. I am wondering why, since it would not be easy for mysql to keep a counter that will be updated in all insertions, updates and deletes?
and is there any way to improve this query? I used the explanation, but did not help much.

Answer 1

As cherouvim noted in the comments, it depends on the storage mechanism.

MyISAM stores table row counts and can be accurate since single MyISAM locks support table locks.

InnoDB , .

http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/

Answer 2

:

1) COUNT (***) COUNT (col)
2) MySQL
3) (*) InnoDB

btw, ?

EDITED: , , . , . , , , , 300 , :

select count(*) FROM
( select 1 FROM _table_ WHERE _conditions_ LIMIT 300 ) AS result

, ; , ( , , " 300 ), 300 , ,

( 6,7 ​​):

1) SELECT count(*) FROM _table_ WHERE START_DATE > '2011-02-01'
4,2 65,4

2) SELECT count(*) FROM ( select 1 FROM _table_ WHERE START_DATE > '2011-02-01' LIMIT 100 ) AS result
100 0,03

, , :

EXPLAIN SELECT count(*) FROM ( select 1 FROM _table_ WHERE START_DATE > '2011-02-01' LIMIT 100 ) AS result