Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Count command too slow [CORE3666] #4016

Closed
firebird-automations opened this issue Nov 17, 2011 · 11 comments
Closed

Count command too slow [CORE3666] #4016

firebird-automations opened this issue Nov 17, 2011 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: dudu (mydudu)

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)

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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?

@firebird-automations
Copy link
Collaborator Author

Commented by: dudu (mydudu)

in the mysql
select count(*) from Tablename;
result 7850802

Total execution time about several second

mysql database file about 23G

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

What is your storange engine in MySQL: MyISAM or InnoDB?

@firebird-automations
Copy link
Collaborator Author

Commented by: dudu (mydudu)

InnoDB is more strong engine, but sqlserver 2000 is faster then firebird.

count 1471628 record execution time also only several second.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: dudu (mydudu)

thanks, I see.

@firebird-automations
Copy link
Collaborator Author

Commented by: Ann Harrison (awharrison)

You could also check the FAQ for ways to maintain an accurate count.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Damyan Ivanov (dam)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I know about that.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant