MySQL COUNT alternative?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

MySQL COUNT alternative?

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: MySQL COUNT alternative?

Post by Christopher »

There is SQL_CALC_FOUND_ROWS / FOUND_ROWS()
(#10850)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL COUNT alternative?

Post by Eran »

Only MyISAM tables have a table cache counter, and it's returned also on COUNT(*)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: MySQL COUNT alternative?

Post 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]
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: MySQL COUNT alternative?

Post 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.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: MySQL COUNT alternative?

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: MySQL COUNT alternative?

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply