Page 1 of 1

MySQL COUNT alternative?

Posted: Thu Feb 24, 2011 10:22 pm
by alex.barylski
I seem to remember a alternative to:

Code: Select all

SELECT COUNT(*) FROM table
A quick GOogle reveals nothing so maybe I am thinking of something else, but I believe the technique returned the tables cached counter instead of executing the SELECT??? ANy ideas?

Cheers,
Alex

Re: MySQL COUNT alternative?

Posted: Thu Feb 24, 2011 11:30 pm
by Christopher
There is SQL_CALC_FOUND_ROWS / FOUND_ROWS()

Re: MySQL COUNT alternative?

Posted: Fri Feb 25, 2011 12:14 am
by Eran
Only MyISAM tables have a table cache counter, and it's returned also on COUNT(*)

Re: MySQL COUNT alternative?

Posted: Fri Feb 25, 2011 1:12 am
by s.dot
I thought I could remember something like this using DESCRIBE, but I guess I was wrong.

Though you could query the information_schema
[text]SELECT `TABLE_ROWS` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'yourdbname' AND `TABLE_NAME` = 'yourtablename'[/text]

Re: MySQL COUNT alternative?

Posted: Fri Feb 25, 2011 4:12 am
by Weirdan
For innodb you may check cardinality of a primary key:

Code: Select all


mysql> show indexes from Person;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Person |          0 | PRIMARY     |            1 | id          | A         |       24919 |     NULL | NULL   |      | BTREE      |         |
--------------------
The information is not exact, but pretty close to the real number of rows. In this case I actually had 24971 rows in that table.

Re: MySQL COUNT alternative?

Posted: Fri Feb 25, 2011 9:46 am
by alex.barylski
Only MyISAM tables have a table cache counter, and it's returned also on COUNT(*)
I eventually came across a few forum posts on mySQL about that very issue and the un-reliability so I guess I"ll just use COUNT(*) :0

Cheers,
Alex

Re: MySQL COUNT alternative?

Posted: Fri Feb 25, 2011 12:36 pm
by pickle
If you're worried about speed, just count the primary key. I believe I read somewhere that COUNT(*) is slower because it needs to count the rows. COUNT(`whatever your primary key`) may be faster because it can just access the index for that column