How to retrieve row count in large table?

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

Moderator: General Moderators

User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

Weirdan wrote:
testing on a table with only 6 rows isn't the most accurate test
as well as testing without purging the caches between test runs :)
I do not understand when you saying purging caches...why caches have to be cleared???:roll:

thanks for your suggestions weirdan and pimptastic, unfortunately I do not have such a big table, if anyone has got it you can post the results.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

why caches have to be cleared???
Because the second query could use some cached info from first one. Thus, you get distorted picture of performace of the second query.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

thanks for your suggestions weirdan and pimptastic, unfortunately I do not have such a big table, if anyone has got it you can post the results.
MySQL 4.0.25, MyISAM, table with ~120000 rows. There's no difference, both count(*) and count(id) finish in 0.00 sec. Id field is indexed (therefor row count is read directly from index).
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

what does indexes have to do with row counts??? Do they store row value separately?

EDIT: I did not mean row values...I meant rows count..
Last edited by raghavan20 on Thu Feb 09, 2006 2:59 pm, edited 1 time in total.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

raghavan20 wrote:what does indexes have to do with row counts???
MySQL manual wrote: Indexes are used to find rows with specific column values quickly.
MySQL manual wrote: MySQL uses indexes for these operations:
[...]
To eliminate rows from consideration.
[...]

Code: Select all

SELECT COUNT(`id`) FROM `table`; -- is equivalent to
SELECT COUNT(*) FROM `table` WHERE `id` IS NOT NULL; -- and index over the `id` column will be used to evaluate WHERE condition
raghavan20 wrote:Do they store row value separately?
For some column types, yes.
MySQL manual wrote: In some cases, a query can be optimized to retrieve values without consulting the data rows. If a query uses only columns from a table that are numeric and that form a leftmost prefix for some key, the selected values may be retrieved from the index tree for greater speed
Post Reply