Page 2 of 2

Posted: Thu Feb 09, 2006 5:21 am
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.

Posted: Thu Feb 09, 2006 5:26 am
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.

Posted: Thu Feb 09, 2006 5:30 am
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).

Posted: Thu Feb 09, 2006 1:13 pm
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..

Posted: Thu Feb 09, 2006 1:27 pm
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