473,372 Members | 1,062 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

"select count(*) from contacts" is too slow!

Why does '*select count(id) from "tblContacts"'* do a sequential scan
when the field '*id*' is indexed using a btree?

MySql simply looks at the index which is keeping a handy record of the
number of rows.

Can anybody explain how and why postgres does this query like it does?

Many thanks

Paul

Nov 12 '05 #1
7 9114
A long time ago, in a galaxy far, far away, pa********@clockltd.com (Paul Serby) wrote:
Why does 'select count(id) from "tblContacts"' do a sequential scan
when the field 'id' is indexed using a btree? MySql simply looks at
the index which is keeping a handy record of the number of rows.
Can anybody explain how and why postgres does this query like it
does?


Look into the semantics of MVCC (MultiVersion Concurrency Control);
that (otherwise useful) feature prevents having any such "handy
record."
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/spreadsheets.html
Developmental Psychology
"Schoolyard behavior resembles adult primate behavior because "Ontogeny
Recapitulates Phylogeny" doesn't stop at birth."
-- Mark Miller
Nov 12 '05 #2
On Tue, 7 Oct 2003, Paul Serby wrote:
Why does '*select count(id) from "tblContacts"'* do a sequential scan
when the field '*id*' is indexed using a btree?

MySql simply looks at the index which is keeping a handy record of the
number of rows.

Can anybody explain how and why postgres does this query like it does?


It's a FAQ I believe.

MySQL can tell you from it's index because it doesn't care if it gives you the
right number or not.
--
Nigel Andrews

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #3
On Tue, 7 Oct 2003, Paul Serby wrote:
Why does '*select count(id) from "tblContacts"'* do a sequential scan
when the field '*id*' is indexed using a btree?

MySql simply looks at the index which is keeping a handy record of the
number of rows.

Can anybody explain how and why postgres does this query like it does?


Because the index doesn't contain enough information to determine if a
particular row is visible to your transaction or not. It would have to go
read the table to find that out, at which point using the index doesn't
help. There's been a recent discussion of this on one of the lists
(either -general or -performance I'd guess) that you might want to look up
in the archives.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #4
> MySQL can tell you from it's index because it doesn't care if it gives you the
right number or not.


Under what circumstances would MySQL give the wrong number?
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #5
A long time ago, in a galaxy far, far away, no***@celery.tssi.com wrote:
MySQL can tell you from it's index because it doesn't care if it gives you the
right number or not.


Under what circumstances would MySQL give the wrong number?


It would give the wrong number under _every_ circumstance where there
are uncommitted INSERTs or DELETEs.
--
select 'cbbrowne' || '@' || 'ntlug.org';
http://www3.sympatico.ca/cbbrowne/sap.html
Appendium to the Rules of the Evil Overlord #1: "I will not build
excessively integrated security-and-HVAC systems. They may be Really
Cool, but are far too vulnerable to breakdowns."
Nov 12 '05 #6
Christopher Browne wrote:
A long time ago, in a galaxy far, far away, no***@celery.tssi.com wrote:
MySQL can tell you from it's index because it doesn't care if it gives you the
right number or not.


Under what circumstances would MySQL give the wrong number?

It would give the wrong number under _every_ circumstance where there
are uncommitted INSERTs or DELETEs.


Give them some credit. I just double checked:

Using mysql 4.0.14 + innodb and transactions,

select count(*) from foo;

does not count uncommited INSERTs.

Heck, even using myisam, mysql's count(*)'s still accurate, since all
INSERTs, etc are autocommitted.

--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386
GNU/Linux
12:00pm up 287 days, 3:33, 7 users, load average: 6.93, 6.31, 6.16

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/hOFWNYbTUIgzwfARAgl0AKCo9bW9anPN6ZUYw0sA5KyzAQDZZw Cgy/tY
/eZY6YK5bhWyljVN9N5dZzY=
=kWAM
-----END PGP SIGNATURE-----

Nov 12 '05 #7

Ang Chin Han <an***@bytecraft.com.my> writes:
Heck, even using myisam, mysql's count(*)'s still accurate, since all INSERTs,
etc are autocommitted.


That's sort of true, but not the whole story. Even autocommitted transactions
can be pending for a significant amount of time. The reason it's accurate is
because with mysql isam tables all updates take a table level lock. So there's
never a chance to select the count while an uncommitted transaction is
pending, even if the update takes a long time.

This is simple and efficient when you have low levels of concurrency. But when
you have 4+ processors or transactions involving lots of disk i/o it kills
scalability.

I'm curious how it's implemented with innodb tables. Do they still take a
table-level lock when committing to update the counters? What happens to
transactions that have already started, do they see the new value?

Actually it occurs to me that that might be ok for read-committed. Is there
ever a situation where a count(*) needs to represent an old snapshot in
read-committed? It has to for long-running selects, but if the count(*) itself
is always fast that need should never arise, just shared-lock and read the
value and unlock.

In order words, imagine if you had every transaction keep a net delta of rows
for every table and at commit time locked the entire table and updated the
count. The lock would be a point of contention but it would be very fast since
it would only have to update an integer with a precalculated adjustment. In
read-committed mode that would always be a valid value. (The transaction would
have to apply its own deltas I guess.)

--
greg
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
by: ian justice | last post by:
Before i post actual code, as i need a speedyish reply. Can i first ask if anyone knows off the top of their head, if there is a likely obvious cause to the following problem. For the moment i've...
16
by: lkrubner | last post by:
Are there any benchmarks on how much an extra, unneeded VARCHAR, CHAR, INT, BIGINT, TEXT or MEDIUMTEXT slows down a database call with MySql? PostGre info would also be useful. I'm trying to...
1
by: Son KwonNam | last post by:
When I try <xsl:value-of select="count('/GROUPS/GROUP')"/> on JSTL <x:transform> tag, I got the following error message. org.apache.jasper.JasperException: Can not convert #STRING to a NodeList!...
3
by: Rabun | last post by:
Heres one that is giving me fits ( = = Access newbie), more than likely something simple that I blew right over . . . any help is appreciated - I have a report based on a query, with several...
2
by: Simon Harvey | last post by:
Hi all, I have to use drop downs in a number of places in my applications. These drop downs are databound but I can't seem to see how to add a new item (eg "Select" or something like that) to...
3
by: divya | last post by:
Hi, I have a table tblbwday with 2 fields Name and Birthday.I have written this script for displaying evryday names of the people on that day. <% set objConn...
5
by: Lennart | last post by:
I really like the construction: select * from new table (update ....) X but I noticed that it cant be used as: insert into T select * from new table (update ....) X because of:
1
by: Patrick A | last post by:
All, Can anyone point me towards the easiest technology or technique for: Importing a table with the same name (Contacts) from 500 different databases, all with different names. (I am in...
5
by: gflor16 | last post by:
Problem: I have this code to run a word counter. But I have a problem when I hit the enter key, it doesn't give me any output of how many chars or words. ''' <summary> ''' Returns Word...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.