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-issue-importer opened this issue Nov 17, 2011 · 11 comments
Closed

Count command too slow [CORE3666] #4016

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

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2011

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?

Loading

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2011

Commented by: dudu (mydudu)

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

Total execution time about several second

mysql database file about 23G

Loading

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2011

Commented by: @dyemanov

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

Loading

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2011

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.

Loading

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2011

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.

Loading

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2011

Modified by: @dyemanov

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

resolution: Won't Fix [ 2 ]

Loading

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2011

Commented by: dudu (mydudu)

thanks, I see.

Loading

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2011

Commented by: Ann Harrison (awharrison)

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

Loading

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2011

Modified by: @pcisar

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

Loading

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2011

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.

Loading

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 18, 2011

Commented by: @dyemanov

I know about that.

Loading

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

Successfully merging a pull request may close this issue.

None yet
1 participant