Jim Morris, on Friday, November 1, 2019 12:07 PM, wrote...
>
> Using a sub-select should work
>
> select
>
> (select count(n) from t0) as "t0_count",
>
> (select count(n) from t1) as "t1_count",
>
> (select count(n) from t2) as "t2_count"
>
> ;
Thanks. Works.
josé
> On 11/1/2019 9:07 AM, Jose
Using a sub-select should work
select
(select count(n) from t0) as "t0_count",
(select count(n) from t1) as "t1_count",
(select count(n) from t2) as "t2_count"
;
On 11/1/2019 9:07 AM, Jose Isaias Cabrera wrote:
> Jose Isaias Cabrera, on Friday, November 1, 2019 11:51 AM, wrote...
>>
>> Richar
Jose Isaias Cabrera, on Friday, November 1, 2019 11:51 AM, wrote...
>
>
> Richard Hipp, on Friday, November 1, 2019 11:41 AM, wrote...
> >
> > On 11/1/19, Jose Isaias Cabrera, on
> > > sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1
> > > AS
> > > b LEFT JOIN t2 AS c;
>
Richard Hipp, on Friday, November 1, 2019 11:41 AM, wrote...
>
> On 11/1/19, Jose Isaias Cabrera, on
> > sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS
> > b LEFT JOIN t2 AS c;
> > 3375|3375|3375
> >
> > Huh? I expected the result:
> >
> > 15|15|15
>
> You did a thre
On 11/1/19, Jose Isaias Cabrera wrote:
> sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS
> b LEFT JOIN t2 AS c;
> 3375|3375|3375
>
> Huh? I expected the result:
>
> 15|15|15
You did a three-way join on tables with 15 rows each. 15x15x15 is 3375.
A LEFT JOIN without
Interesting! I appreciate the detailed response. I don't think the shadow table
digging fits our risk profile exactly :), but it's interesting to know where to
look if we want to check ourselves. I realized after rereading all of this that
ultimately we want to keep track of the max rowid insert
On 19/10/62 06:31, Ben Asher wrote:
Hello! I'm trying to write some code to keep an external content table in
sync with the index. To do this, I need to be able to get some state about
the index: either how many rows have been inserted so far or the max rowid
that has been inserted into the inde
I should have included this in the first place. Here are the queries used
to setup this table and an example:
CREATE TABLE text (text TEXT NONNULL);
CREATE VIRTUAL TABLE text_fts_index USING fts5(text, content=text,
content_rowid=rowid, prefix='2 3 4 5 6 7 8 9 10', tokenize='unicode61');
INSERT IN
@mailinglists.sqlite.org
Subject: Re: [sqlite] count optimisation
On 2018/03/15 12:20 PM, x wrote:
> select count(*) from TblA cross join TblB
>
> is over 200 times slower than
>
> select (select count(*) from TblA) * (select count(*) from TblB);
This is a human-level optimisation, it&
On 2018/03/15 12:20 PM, x wrote:
select count(*) from TblA cross join TblB
is over 200 times slower than
select (select count(*) from TblA) * (select count(*) from TblB);
This is a human-level optimisation, it's not efficient for the database
engine to do the optimisation.
By human-level I
On 30 Dec 2016, at 11:15am, MONSTRUO Hugo González
wrote:
> [URL for a copy of his database]
>
> SELECT COUNT (*) FROM bm_ppal
> 59.046 seconds of delay
>
> SELECT COUNT (*) FROM bm_ppal ORDER BY nbmId
> 1.128 minutes of delay
>
> SELECT COUNT (nbmId) FROM bm_ppal ORDER BY nbmId
> 1.089 minu
Hi,
thanks: Donald Griggs, John Gillespie, Simon Slavin
I have a phone book (bm_ppal), 726.000 rows, 10 columns
This phone book have this columns
Name Declared Type Type Size
nbmId INTEGER INTEGER
nbmCodigo VARCHAR (6) VARCHAR 6
abmNombre VARCHAR (320) VARCHAR 320
abmNombrePlano VARCHAR (320) VA
On 26 Dec 2016, at 3:14pm, MONSTRUO Hugo González
wrote:
> I have a table with 726.000 registers.
>
> SELECT COUNT(*) FROM MyTable << is very slowly
>
> SELECT COUNT (RowId) FROM MyTable ORDER BY PrimaryIndex << is very FAST
While this is not a bug in SQLite, since it reaches the right answe
Hello, Hugo,
Regarding: "I have a table with 726.000 registers."
1) I assume that you mean what others call "rows" correct? (and not
columns, I hope)
2) Regarding: "SELECT COUNT(*) FROM MyTable << is very slowly"
As I understand it, that should be as fast as SELECT COUNT (RowId) as of
late
> Which is the fastest way to count the records of a table. ? And records
> that meet a condition?
I have a table with 726.000 registers.
SELECT COUNT(*) FROM MyTable << is very slowly
SELECT COUNT (RowId) FROM MyTable ORDER BY PrimaryIndex << is very FAST
SELECT COUNT(RowId) FROM MyTable WHERE
On 24 Dec 2016, at 10:52am, MONSTRUO Hugo González
wrote:
> Which is the fastest way to count the records of a table. ? And records
> that meet a condition?
SELECT COUNT(*) FROM MyTable
SELECT COUNT(*) FROM MyTable WHERE a = b
There is special optimization for COUNT with a * which can some
es
will be identical; but the contents reflect the last record read if the
statement requires a sort operation).
-Ursprüngliche Nachricht-
Von: Dominique Devienne [mailto:ddevie...@gmail.com]
Gesendet: Mittwoch, 27. November 2013 09:45
An: General Discussion of SQLite Databa
On Wed, Nov 27, 2013 at 9:35 AM, Clemens Ladisch wrote:
> veeresh kumar wrote:
> > I see that in release history for 3.8.1 below item.
> > "Estimate the sizes of table and index rows and use the smallest
> applicable B-Tree for full scans and "count(*)" operations."
> >
> > Does it mean that perf
veeresh kumar wrote:
> I see that in release history for 3.8.1 below item.
> "Estimate the sizes of table and index rows and use the smallest applicable
> B-Tree for full scans and "count(*)" operations."
>
> Does it mean that performance of count(*) has been improved in 3.8.1
Yes.
> and if yes
The answer to you exact question is "Yes". Didn't help much did it?
But I will at least start off by giving some hints. You need to do an inner
join on the common column (service_no). You need to use the HAVING clause
to select only those entries with more than 2 entries in the result table
(whic
On 9/3/2013 6:52 PM, ChingChang Hsiao wrote:
What is the count of services have more than 2 service_port(sp) and type is
e-lan? The answer is 1. Could it be described as one sql statement?
select count(*) from service_table s
where service_type = 'e-lan' and
(select count(*) from service_por
If you get no rows returned, you could query SELECT COUNT(*) FROM table1;
(If you get rows returned, you know that the number skipped is the size of
your offset.)
On Thu, Dec 6, 2012 at 11:44 AM, Steffen Mangold <
steffen.mang...@balticsd.de> wrote:
> Hi,
>
> I want to know if there is a possibi
On Mon, Sep 17, 2012 at 06:03:12PM -0600, Keith Medcalf scratched on the wall:
> > There it is again. What does length(NULL) mean ? Should it be NULL because
> > anything derived from "Don't know" is "Don't know" ? Or should it be a non-
> > negative integer representing how long the NULL value
> There it is again. What does length(NULL) mean ? Should it be NULL because
> anything derived from "Don't know" is "Don't know" ? Or should it be a non-
> negative integer representing how long the NULL value is ? I may have missed
> it, but I don't think the answer is in SQL92. The only sol
On Sep 17, 2012, at 5:09 PM, Simon Slavin wrote:
> I suspect that this is what leads to different SQL engines implementing
> count() in different ways.
I suspect that you are indeed very confused :D
The issue at hand is not whether or not count( * ) is implemented differently
(it's not), but
On Sep 17, 2012, at 5:23 PM, "Keith Medcalf" wrote:
> I don't know if there is a standard for treating empty character strings as
> if they were not-present values without first applying one of the builtin
> functions designed to deal with NULL. However, my opinion is that SQLite is
> correc
Simon Slavin wrote:
> What does length(NULL) mean ?
When there is no string, there is no string length.
> I don't think the answer is in SQL92.
| 6.6
| [...]
| General Rules
| [...]
| 4) If a is specified, then
|Case:
|a) Let S be the . If the value of S is
| not the null value,
On 17 Sep 2012, at 4:30pm, "Black, Michael (IS)" wrote:
> sqlite> select count(*) from t where length(a) = 0;
> 1
There it is again. What does length(NULL) mean ? Should it be NULL because
anything derived from "Don't know" is "Don't know" ? Or should it be a
non-negative integer represent
ng Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of John Clegg [john.cl...@nailsea.net]
Sent: Monday, September 17, 2012 10:05 AM
To: General Discussion of SQLite Database
Subje
; boun...@sqlite.org] On Behalf Of Black, Michael (IS)
> Sent: Monday, 17 September, 2012 08:50
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Count(*) help
>
> Or just fix the existing table:
>
> update members set year2007=NULL where year2007='&#
On 17 Sep 2012, at 4:05pm, John Clegg wrote:
> Yes, that was the problem thanks. Even though sqlitebrowser declared them
> as empty with '' !
No, actually that's your problem. '' is exactly the way to define something as
an empty string. To supply a null you would have to supply NULL, withou
On 17 Sep 2012, at 3:50pm, "Black, Michael (IS)" wrote:
> I'll note that Oracle doesn't count ''. whereas sqlite3 does. Does the SQL
> standard say anything about what a "NULL" value is? And who's correct here
> if there is a standard?
I believe that the values are fine. The problem in SQL
Yes, that was the problem thanks. Even though sqlitebrowser declared them
as empty with '' !
On 17 September 2012 15:50, Black, Michael (IS) wrote:
> Or just fix the existing table:
>
> update members set year2007=NULL where year2007='';
> update members set year2008=NULL where year2008='';
> upd
Or just fix the existing table:
update members set year2007=NULL where year2007='';
update members set year2008=NULL where year2008='';
update members set year2009=NULL where year2009='';
update members set year2010=NULL where year2010='';
update members set year2011=NULL where year2011='';
update
John Clegg wrote:
> I have a table Members with 896 rows and a text field "Year2012". It
> contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
> in sqlitebrowser as "empty")
>
> Back in the olden days when this table was in Access, select
> count("Year2013") from Members u
lite-users-boun...@sqlite.org] on
behalf of John Clegg [john.cl...@nailsea.net]
Sent: Monday, September 17, 2012 1:59 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Count(*) help
OK thanks folks. Here is the full query (which is why I can't use WHERE
clauses! It has always
Hi John,
If you zip the file (it sounds it is only small) and mail it to me or
to this list and tell what answer you want I (or somebody else) will
post the SQL.
RBS
On Mon, Sep 17, 2012 at 7:59 AM, John Clegg wrote:
> OK thanks folks. Here is the full query (which is why I can't use WHERE
> c
OK thanks folks. Here is the full query (which is why I can't use WHERE
clauses! It has always worked before.. I have removed the double-quoted
but it makes no difference.
SELECT COUNT( "Year2007" ), COUNT( "Year2008" ), COUNT( "Year2009" ),
COUNT( "Year2010" ), COUNT( "Year2011" ), COUNT( "Ye
> PS Note that it is count(X) and not count("X")
Count(X) and Count("X") are identical if X is a column in the table (or join)
from which you are counting, and X does not contain embeded spaces, special
characters, or other inanities. count(*) counts rows, and count('X') or
count(1) [or any
On 9/16/2012 9:17 AM, John Clegg wrote:
I have a table Members with 896 rows and a text field "Year2012". It
contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
in sqlitebrowser as "empty")
Back in the olden days when this table was in Access, select
count("Year2013") fro
The Year2012 field is not null, it is an empty string (that is, '', which is
not NULL).
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of John Cl
On 16-09-2012 18:21, Marcus Ilgner wrote:
On So, 2012-09-16 at 17:17 +0100, John Clegg wrote:
I have a table Members with 896 rows and a text field "Year2012". It
contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
in sqlitebrowser as "empty")
Back in the olden days when
John Clegg wrote:
> I have a table Members with 896 rows and a text field "Year2012". It
> contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
> in sqlitebrowser as "empty")
>
> Back in the olden days when this table was in Access, select
> count("Year2013") from Members use
Hi John,
Funny seeing you here on the SQLite forum.
Are these by any chance the ISUG members?
Doing a count without a WHERE clause is always likely to give different results
with the various SQL implications as far as I know.
Why not add a WHERE?
RBS
On Sun, Sep 16, 2012 at 5:17 PM, John Clegg
On So, 2012-09-16 at 17:17 +0100, John Clegg wrote:
> I have a table Members with 896 rows and a text field "Year2012". It
> contains "Paid" 156 times, "Comp" 13 times and the rest are null (confirmed
> in sqlitebrowser as "empty")
>
> Back in the olden days when this table was in Access, select
>
On 6/14/12 1:00 PM, "Igor Tandetnik" wrote:
>On 6/14/2012 2:00 PM, Duquette, William H (318K) wrote:
>> What I want to do is find a_id's for which c contains no rows with the
>>matching b_id in which the flag column is 1.
>
>Why don't you just say that?
>
>select a_id from a
>where b_id not in (
On 6/14/2012 2:00 PM, Duquette, William H (318K) wrote:
What I want to do is find a_id's for which c contains no rows with the matching
b_id in which the flag column is 1.
Why don't you just say that?
select a_id from a
where b_id not in (select b_id from c where flag = 1);
--
Igor Tandetnik
On 6/14/12 11:06 AM, "Pavel Ivanov" wrote:
>> SELECT a_id, count(*)
>> FROM a LEFT OUTER JOIN c USING (b_id)
>> WHERE c.flag = 1
>> GROUP BY a_id
>>
>> I get a positive count if there are there are matching rows with flag =
>>1; but if an a_id has no matching rows in c with flag=1, I get nothing
> SELECT a_id, count(*)
> FROM a LEFT OUTER JOIN c USING (b_id)
> WHERE c.flag = 1
> GROUP BY a_id
>
> I get a positive count if there are there are matching rows with flag = 1;
> but if an a_id has no matching rows in c with flag=1, I get nothing at all.
> What I want is a row with the a_id and
Did you do multiple runs and average? And how are you measuring time? Did you
do buffered reads?
And you're MB/sec isn't accurate for the count() test. I think it was already
said that count() doesn't read the entire record.
The SQLite numbers are due to SQLite caching. If you make page size
On Sun, Feb 27, 2011 at 3:11 PM, Max Vlasov wrote:
> Hmm, yesterday something struck me I can do similar tests on an Asus T91MT
> having SSD as the storage. I got similar timings to Greg's. So it seems like
> page size is a very sensitive parameter for solid state drives. Looks like
> having the p
On Sat, Feb 26, 2011 at 1:03 AM, Greg Barker wrote:
> harddrive. Can you confirm this?
>
> > Giving this, if the fastest is 3.6 seconds, you have a very fast
> I can confirm this, my tests were run on a machine that uses a solid state
> drive.
>
>
Hmm, yesterday something struck me I can do simi
On 25 Feb 2011, at 10:03pm, Greg Barker wrote:
> Thanks for the responses guys.
>
>> I would never have any table with 150 columns. It should be possible to
>> keep the schema for your table in your head.
>
> Unfortunately those are the cards I have been dealt. The columns are just
> buckets o
Thanks for the responses guys.
> I would never have any table with 150 columns. It should be possible to
keep the schema for your table in your head.
Unfortunately those are the cards I have been dealt. The columns are just
buckets of data (bucket1, bucket2, bucket3, etc). Each bucket could be 1
On Fri, Feb 25, 2011 at 1:51 AM, Greg Barker wrote:
> I ran my tests again, this time setting the cache_size based on the
> page_size. For some reason I wasn't able to replicate the timing I got
> before for the 1024 page size (40.2 seconds) which is really confusing me,
> since I was careful to
On 24 Feb 2011, at 10:51pm, Greg Barker wrote:
> What do
> you do if there could be anywhere between 30-150 columns?
I would never have any table with 150 columns. It should be possible to keep
the schema for your table in your head.
> Optimizing performance
> for an application where both th
I ran my tests again, this time setting the cache_size based on the
page_size. For some reason I wasn't able to replicate the timing I got
before for the 1024 page size (40.2 seconds) which is really confusing me,
since I was careful to make sure nothing was running in the background while
I was te
Average payload per entry for my test table was 409.00. Sounds about right
since the db has 4 columns and each is filled with a random string of length
100.
I've uploaded the full output from the sqlite3_analyzer for a few different
page sizes:
http://fletchowns.net/files/1024-analyzer.txt
http:/
t; To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
> Is the record size you refer to here the same as the "Average payload per
> entry" that sqlite3_analyzer determines for me?
>
> On Wed, Feb 23, 2011 at 5:09 AM, Max Vlasov wrote:
&g
Yes, Greg, please post this value for this table you use in count query
Max
On Wed, Feb 23, 2011 at 9:58 PM, Greg Barker wrote:
> Is the record size you refer to here the same as the "Average payload per
> entry" that sqlite3_analyzer determines for me?
>
> On Wed, Feb 23, 2011 at 5:09 AM, Max
Is the record size you refer to here the same as the "Average payload per
entry" that sqlite3_analyzer determines for me?
On Wed, Feb 23, 2011 at 5:09 AM, Max Vlasov wrote:
> Greg, you should also take the record size into account. My hypothesis is
> that if your record is comparatively small (s
2011 10:47:03 -0500
>> From: pri...@gmail.com
>> To: t...@djii.com; sqlite-users@sqlite.org
>> Subject: Re: [sqlite] COUNT() extremely slow first time!
>>
>> The SQLite cache size is in pages (2000 by default), so by increasing
>> the page size 8x, you're
characters):
MyID|MyText
With a page size of 4096, will SQLite put ~200 rows in one page?
> Date: Wed, 23 Feb 2011 10:47:03 -0500
> From: pri...@gmail.com
> To: t...@djii.com; sqlite-users@sqlite.org
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
> The SQLit
The SQLite cache size is in pages (2000 by default), so by increasing
the page size 8x, you're also increasing the cache size and memory
requirements by 8x. Not saying it's a bad thing, just something to be
aware of.
If you want to compare 1K and 8K page size and only compare the effect
page size
On Wed, Feb 23, 2011 at 03:10:02PM +0100, Sven L scratched on the wall:
>
> Does this trick work on the primary key? If not, why?
Yes, all the time.
Defining a column as a PK automatically creates a UNIQUE index
over that column. The only exception is when the column is an
INTEGER PRI
Hello Greg,
I found this to be the case too. The difference between 1K and 8K is
staggering. I default all my windows DB's to 8K now.
Tuesday, February 22, 2011, 1:59:29 PM, you wrote:
GB> I'm currently dealing with a similar issue. I've found that the page_size
GB> PRAGMA setting can have a dr
Does this trick work on the primary key? If not, why?
> From: max.vla...@gmail.com
> Date: Wed, 23 Feb 2011 16:09:04 +0300
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
> On Tue, Feb 22, 2011 at 9:59 PM, Greg Barker wrote:
&g
On Tue, Feb 22, 2011 at 9:59 PM, Greg Barker wrote:
> I'm currently dealing with a similar issue. I've found that the page_size
> PRAGMA setting can have a dramatic effect on how long it takes to "warm up"
> the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column)
> takes 40.2 se
Interesting!
I've been using "PRAGMA page_size = 4096;" in my software. Perhaps I should
increase it and see if I can get a performance gain.
Does it affect INSERTs too?
> Date: Tue, 22 Feb 2011 10:59:29 -0800
> From: fle...@fletchowns.net
> To: sqlite-users@sqlite.or
I'm currently dealing with a similar issue. I've found that the page_size
PRAGMA setting can have a dramatic effect on how long it takes to "warm up"
the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column)
takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes
On Mon, Feb 21, 2011 at 11:35 AM, Jim Wilcoxson wrote:
> On Mon, Feb 21, 2011 at 11:05 AM, Sven L wrote:
>>
>> Thank you for your detailed explanation!
>> First, can you please tell me how to purge the cache in Windows 7? This
>> could be very useful for my tests!
>
> Sorry, dunno for Windows.
lav...@bigfraud.org
> Date: Mon, 21 Feb 2011 16:56:01 +
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
>
> On 21 Feb 2011, at 4:35pm, Jim Wilcoxson wrote:
>
> > On Mon, Feb 21, 2011 at 11:05 AM, Sven L wrote:
> >>
>
On 21 Feb 2011, at 4:35pm, Jim Wilcoxson wrote:
> On Mon, Feb 21, 2011 at 11:05 AM, Sven L wrote:
>>
>> Thank you for your detailed explanation!
>> First, can you please tell me how to purge the cache in Windows 7? This
>> could be very useful for my tests!
>
> Sorry, dunno for Windows. On M
is just what happens on my system (Mac OSX),
and may have nothing at all to do with the performance on yours.
Jim
>
> Thanks again!
>
>
>> Date: Mon, 21 Feb 2011 10:17:03 -0500
>> From: pri...@gmail.com
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite]
On Mon, Feb 21, 2011 at 03:37:50PM +0100, Sven L scratched on the wall:
> I've learnt that COUNT(*) is slower than COUNT(ID),
That's usually not true.
> since * means the engine has to traverse all columns
Actually, count(*) is the one case when the engine does *not* need to
traverse any
>
>> > I've learnt that COUNT(*) is slower than COUNT(ID), since * means the
>> > engine has to traverse all columns and it might even return another value
>> > if there are NULL-values...
>> >
>> > Also, this is quite interesting:
&g
That's not possible? :P
Thanks again!
> Date: Mon, 21 Feb 2011 10:17:03 -0500
> From: pri...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
> This is a common issue on the mailing list. The first time you do
> cou
t; sqlite> EXPLAIN QUERY PLAN SELECT COUNT(ItemID) FROM Items;
> > 0|0|0|SCAN TABLE Items (~100 rows)
> > sqlite> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM Items;
> > sqlite>
> >
> > I would expect an index scan on the first statement. The second statement
>
SCAN TABLE Items (~100 rows)
> sqlite> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM Items;
> sqlite>
>
> I would expect an index scan on the first statement. The second statement
> tells me nada?!
>
> Thanks for your help!
>
>
>> From: slav...@bigfraud.org
&g
!
Thanks for your help!
> From: slav...@bigfraud.org
> Date: Mon, 21 Feb 2011 14:24:50 +
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
>
> On 21 Feb 2011, at 2:23pm, Sven L wrote:
>
> > SELECT COUNT(ItemID) FROM
On 21 Feb 2011, at 2:23pm, Sven L wrote:
> SELECT COUNT(ItemID) FROM Items;
>
> This takes around 40 seconds the first time! WHY?!
Try again, doing everything identically except that instead of the above line
use
SELECT COUNT(*) FROM Items;
Simon.
I can get a big speed up of COUNT if I first do a VIEW of what I have
to count and than make select COUNT on the view.
Without VIEW: 9 Minutes
With VIEW: 8 Seconds!
Il 24/09/2010 10.58, Martin Engelschalk ha scritto:
>
> Am 24.09.2010 10:38, schrieb Michele Pradella:
>> ok, thank you for th
Am 24.09.2010 10:38, schrieb Michele Pradella:
>ok, thank you for the advices, I'll try to use a TRIGGER.
> The DB already has an index.
> Anyway if I have to count something like this:
> select COUNT(*) from logs WHERE DateTime<=yesterday
> I can't do it with a TRIGGER
No, but in this case a
ok, thank you for the advices, I'll try to use a TRIGGER.
The DB already has an index.
Anyway if I have to count something like this:
select COUNT(*) from logs WHERE DateTime<=yesterday
I can't do it with a TRIGGER
Il 24/09/2010 10.29, Martin Engelschalk ha scritto:
>Hello Michele,
>
> sqlit
Quoth Michele Pradella , on 2010-09-24 10:13:59
+0200:
> I have an SQLite DB of about 9GB with about 2.500.000 records.
> I can't understand why the "select COUNT(*) from log" statement is
> extremely slow, it takes me about 9-10 minutes!
> I try with:
> select COUNT(1) from logs
> select COUNT
Hello Michele,
sqlite does not remember the number of records in a table. Therefore,
counting them requires to scan the full table, which explains the slow
perfornamce.
This topic has been discussed previously in this list. See
http://www.mail-archive.com/sqlite-users@sqlite.org/msg10279.htm
On Sep 24, 2010, at 3:13 PM, Michele Pradella wrote:
> I have an SQLite DB of about 9GB with about 2.500.000 records.
> I can't understand why the "select COUNT(*) from log" statement is
> extremely slow, it takes me about 9-10 minutes!
In SQLite, count() is obliged to traverse the entire table
I forgot to tell you that I'm using the sqlite3 shell to test the
statement, and I got same slowness with DB in use or DB not in use
Il 24/09/2010 10.13, Michele Pradella ha scritto:
>I have an SQLite DB of about 9GB with about 2.500.000 records.
> I can't understand why the "select COUNT(
On Thu, Jul 29, 2010 at 00:47, Igor Tandetnik wrote:
> Peng Yu wrote:
>> I want to select only the rows where if S column are the same, R1
>> column is the same and R2 column is the same.
>
> select * from mytable where s in
> (select s from mytable
> group by s
> having min(r1)=max(r1) and min
Peng Yu wrote:
> Suppose that I have a table of 4 columns.
>
> S R1 R2 T
>
> s1r1 r2 t1
> s1r1 r2 t2
> s2r3 r4 t5
> s2r5 r4 t6
> s3r6
I'm not sure what do you want to return for the case like this:
s1r1 r2 t1
s1r1 r2 t2
s1r1 r3 t2
But for your initial request the following query will be good:
select t1.*
from table_name t1,
(select s, count(*) cnt
fr
Peng Yu wrote:
> I use || to count distinct on multiple fields. It works for this
> particular example. But I'm not sure if it is robust. Would you please
> let me know if there is any better way?
>
> select distinct value1, value2 from test;
> select count(distinct value1||value2) from test;
se
On 11/12/08, baxy77bax <[EMAIL PROTECTED]> wrote:
>
> hi,
> my question is: Is there a way to count the number of specific data in the
> column of a table, for example:
>
> let say i have a table like this:
> numbers
> 1
> 2
> 2
> 3
> 4
> 4
> 4
> ...
> and i would like to report my da
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I supposed a very fast query even with many records, because COUNT( )
> might do its work on the index only.
Well, it does, but the work is still proportional to the number of
records counted. The index helps to quickly filter out reco
quickly. The often discussed "trigger
COUNT() solution" might not be applicable here.
Daniel
Ursprüngliche Nachricht
Von: [EMAIL PROTECTED]
Datum: 14.07.2008 00:51
An:
Betreff: Re: [sqlite] COUNT() on indexed tables / primary keywith
100'000records
<[EMAIL PR
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Lets say i have 120'000 records and worst case is that upto 90'000
> records match the conditions.
Well, if you count almost all records, why do you expect the query to
run substantially faster than the one that in fact counts all reco
Hello
Lets say i have 120'000 records and worst case is that upto 90'000 records
match the conditions.
Daniel
Ursprüngliche Nachricht
Von: [EMAIL PROTECTED]
Datum: 13.07.2008 17:16
An:
Betreff: Re: [sqlite] COUNT() on indexed tables / primary key with
100'000r
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I have following table with around 100'000 rows / ~10MB on a embedded
> device:
>
> CREATE TABLE 'fs_main' (
> 'fs_recid' INTEGER PRIMARY KEY NOT NULL,
> 'fs_contenttype' INTEGER,
> 'fs_itemtype' INTEGER,
> 'fs_job' INTEGER,
> 'fs_textid
ound 100, the savings would be
> 10x
>
> Regards -- Noah
>
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Scott Hess
> Sent: Friday, April 04, 2008 9:15 AM
> To: General Discussion of SQLite Database
> Sub
On Fri, Apr 04, 2008 at 09:14:52AM -0700, Scott Hess scratched on the wall:
> What I meant when I said "full table scan" is that it has to read at
> least something for every single row in the table. So the following
> are going to be the same:
>
> SELECT COUNT(*) FROM t;
> SELECT COUNT(rowid
1 - 100 of 142 matches
Mail list logo
