Issue Details (XML | Word | Printable)

Key: CORE-3666
Type: Bug Bug
Status: Closed Closed
Resolution: Won't Fix
Priority: Critical Critical
Assignee: Unassigned
Reporter: dudu
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Count command too slow

Created: 17/Nov/11 07:56 AM   Updated: 18/Nov/11 04:58 AM
Component/s: None
Affects Version/s: None
Fix Version/s: None

Environment: Win2003 r2 sp2, firebird 2.5.1,cpu Xeon E5620 * 4 ,ram 4G


 Description  « Hide
select count(*) from Tablename;
result 1585294

4779559 fetches, 0 marks, 804283 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 0 index, 1585294 seq.
Delta memory: 7168 bytes.
Total execution time: 0:01:21 (hh:mm:ss)

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 17/Nov/11 08:00 AM - edited
COUNT can be calculated only via a full table scan, please read the articles about MGA/MVCC. Do you believe that almost 4GB can be read from the disk faster?

dudu added a comment - 17/Nov/11 08:51 AM - edited
in the mysql
select count(*) from Tablename;
result 7850802

Total execution time about several second

mysql database file about 23G

Dmitry Yemanov added a comment - 17/Nov/11 09:20 AM
What is your storange engine in MySQL: MyISAM or InnoDB?

dudu added a comment - 17/Nov/11 09:24 AM - edited
InnoDB is more strong engine, but sqlserver 2000 is faster then firebird.

count 1471628 record execution time also only several second.

Dmitry Yemanov added a comment - 17/Nov/11 09:47 AM
As suggested earlier, you should learn the Firebird MGA implementation (you may also refer to the PostgreSQL docs, as its MVCC implementation is quite similar and it also has the COUNT being slow) before comparing apples with oranges. This is not a bug. And if your application hardly relies on fast counting over the big tables, Firebird is definitely a wrong choice for you.

dudu added a comment - 17/Nov/11 09:59 AM
thanks, I see.

Ann Harrison added a comment - 17/Nov/11 04:33 PM
You could also check the FAQ for ways to maintain an accurate count.

Damyan Ivanov added a comment - 17/Nov/11 08:25 PM
FYI: PostgreSQL is changing the handling of count(*) using index scans. See http://rhaas.blogspot.com/2011/10/fast-counting.html

There is no "silver bullet", but still the article is worth a read.

Dmitry Yemanov added a comment - 18/Nov/11 04:58 AM
I know about that.