Optimization about count(*)

#1

sql:
select count(*) from t where id > 10 and id < 1000000;
In table t, there is an B-tree index on id field, this sql will read
hundreds of thousandes of lines from the storage engine, this is a
time-consuming operation.
If tell the storage engine computing the count in the range, does it
have higher performance? (using records_in_range() api and so on)

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw

#2

Hi, ??????!

On Jan 09, ?????? wrote:

Quote:

sql:
select count(*) from t where id > 10 and id < 1000000;
In table t, there is an B-tree index on id field, this sql will read
hundreds of thousandes of lines from the storage engine, this is a
time-consuming operation.
If tell the storage engine computing the count in the range, does it
have higher performance? (using records_in_range() api and so on)

There's no API for that in MySQL yet.
records_in_range() is approximate, it cannot be used for count(*).

Regards,
Sergei

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw

#3

I think MySQL just need to read the B-Tree index for this count(*).

On 01/09/2011 11:41 PM, Sergei Golubchik wrote:

Quote:

Hi, ??????!

On Jan 09, ?????? wrote:

sql:
select count(*) from t where id> 10 and id< 1000000;
In table t, there is an B-tree index on id field, this sql will read
hundreds of thousandes of lines from the storage engine, this is a
time-consuming operation.
If tell the storage engine computing the count in the range, does it
have higher performance? (using records_in_range() api and so on)

There's no API for that in MySQL yet.
records_in_range() is approximate, it cannot be used for count(*).

Regards,
Sergei

--
---
MySQL& Oracle Data Architecture
Oracle ACE
sky000 (AT) gmail (DOT) com
WebSiteï¼šhttp://iSky000.com

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw

#4

Hi, sky.jian!

On Jan 10, sky.jian wrote:

Quote:

I think MySQL just need to read the B-Tree index for this count(*).

Yes, and it does that.
Assuming the engine supports "just reading the index" - not every
storage engine does.

Quote:

On 01/09/2011 11:41 PM, Sergei Golubchik wrote:

sql:
select count(*) from t where id> 10 and id< 1000000;
In table t, there is an B-tree index on id field, this sql will read
hundreds of thousandes of lines from the storage engine, this is a
time-consuming operation.
If tell the storage engine computing the count in the range, does it
have higher performance? (using records_in_range() api and so on)

There's no API for that in MySQL yet.
records_in_range() is approximate, it cannot be used for count(*).

Regards,

Sergei

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw