Re: [sqlite] count(*) slow
... and where will it stop? Someone will then ask for 'SELECT COUNT(*) ...
WHERE ...' changes.
Agreed, leave things as they are.
rayB
|-+>
| | Darren Duncan|
| | <[EMAIL PROTECTED]|
| | can.net> |
| ||
| | 16/09/2005 14:19 |
| | Please respond to|
| | sqlite-users |
| ||
|-+>
>--|
|
|
| To: sqlite-users@sqlite.org
|
| cc:
|
| Subject: Re: [sqlite] count(*) slow
|
>--|
At 8:56 AM -0500 9/15/05, Puneet Kishor wrote:
>Hence, it might be worthwhile maintaining the meta information no
>matter what... most of the folks won't ever notice it, and everyone
>would marvel at how quickly COUNT(*) was returning the results.
You are assuming that everyone wants to do a count(), but many people
don't; for them, putting that in the core slows things down; for
people that do want it sped up, the trigger option is perfectly valid.
I support leaving things the way they are, with no extra meta-info
maintained.
-- Darren Duncan
** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING *
*** Confidentiality and Privilege Notice ***
This e-mail is intended only to be read or used by the addressee. It is
confidential and may contain legally privileged information. If you are not
the addressee indicated in this message (or responsible for delivery of the
message to such person), you may not copy or deliver this message to anyone,
and you should destroy this message and kindly notify the sender by reply
e-mail. Confidentiality and legal privilege are not waived or lost by reason
of mistaken delivery to you.
Qantas Airways Limited
ABN 16 009 661 901
Visit Qantas online at http://qantas.com
Re: [sqlite] count(*) slow
At 8:56 AM -0500 9/15/05, Puneet Kishor wrote: Hence, it might be worthwhile maintaining the meta information no matter what... most of the folks won't ever notice it, and everyone would marvel at how quickly COUNT(*) was returning the results. You are assuming that everyone wants to do a count(), but many people don't; for them, putting that in the core slows things down; for people that do want it sped up, the trigger option is perfectly valid. I support leaving things the way they are, with no extra meta-info maintained. -- Darren Duncan
Re: [sqlite] count(*) slow
> Interesting. But, with the above suggestion, every INSERT or DELETE > would slow down anyway as much as it would have were SQLite to maintain > meta information itself, no? > > . > > Hence, it might be worthwhile maintaining the meta information no > matter what... most of the folks won't ever notice it, and everyone > would marvel at how quickly COUNT(*) was returning the results. I cannot say I have ever used COUNT(*) in any program I have ever created I can say however, I tend to use INSERTS fairly often IMHO, not maintining the meta info (which speeds up a process everyone does at the expense of speed in a process less people do) seems like the correct decision
Re: [sqlite] count(*) slow
On Sep 15, 2005, at 8:43 AM, D. Richard Hipp wrote: On Thu, 2005-09-15 at 13:59 +0100, Da Martian wrote: Hi I have 3 million rows in a table which takes up about 3.1GB on disk. The count(*) is slow. I have run the analyze, but apart from creating the stats table it does nothing. Any reason why this is? Can it be improved ? SQLite always does a full table scan for count(*). It does not keep meta information on tables to speed this process up. Not keeping meta information is a deliberate design decision. If each table stored a count (or better, each node of the btree stored a count) then much more updating would have to occur on every INSERT or DELETE. This would slow down INSERT and DELETE, even in the common case where count(*) speed is unimportant. If you really need a fast COUNT, then you can create a trigger on INSERT and DELETE that updates a running count in a separate table then query that separate table to find the latest count. Interesting. But, with the above suggestion, every INSERT or DELETE would slow down anyway as much as it would have were SQLite to maintain meta information itself, no? If the table were small enough, the overhead because of the above maintenance would not be "visible," and if the table were large enough, then the above overhead would be necessary either via SQLite doing it automatically or the user doing it via the suggested TRIGGER method. Hence, it might be worthwhile maintaining the meta information no matter what... most of the folks won't ever notice it, and everyone would marvel at how quickly COUNT(*) was returning the results. -- Puneet Kishor
Re: [sqlite] count(*) slow
On Thu, 2005-09-15 at 13:59 +0100, Da Martian wrote: > Hi > > I have 3 million rows in a table which takes up about 3.1GB on disk. The > count(*) is slow. > > I have run the analyze, but apart from creating the stats table it does > nothing. > > Any reason why this is? Can it be improved ? SQLite always does a full table scan for count(*). It does not keep meta information on tables to speed this process up. Not keeping meta information is a deliberate design decision. If each table stored a count (or better, each node of the btree stored a count) then much more updating would have to occur on every INSERT or DELETE. This would slow down INSERT and DELETE, even in the common case where count(*) speed is unimportant. If you really need a fast COUNT, then you can create a trigger on INSERT and DELETE that updates a running count in a separate table then query that separate table to find the latest count.
RE: [sqlite] count(*) slow
You'll need to provide more information to get a helpful answer. What version are you using? What indexes are present on the table? How was the table defined? -Tom > -Original Message- > From: Da Martian [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 15, 2005 8:59 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] count(*) slow > > Hi > > I have 3 million rows in a table which takes up about 3.1GB > on disk. The > count(*) is slow. > > I have run the analyze, but apart from creating the stats > table it does > nothing. > > Any reason why this is? Can it be improved ? >
