----- Forwarded by Ben Carlyle/AU/IRSA/Rail on 31/03/2004 10:13 AM -----
Ben Carlyle
30/03/2004 11:17 AM
To: "Balthasar Indermuehle" <[EMAIL PROTECTED]>@CORP
cc:
Subject: RE: [sqlite] Is using max(rowid) instead of count(*) safe?
"Balthasar Indermuehle" <[EMAIL PROTECTED]>
30/03/2004 12:02 AM
To: <[EMAIL PROTECTED]>
cc:
Subject: RE: [sqlite] Is using max(rowid) instead of count(*) safe?
> I created a test database on my laptop (winxp) with my tester app.
> Inserted 1 million records with random data. Here's how fast this works,
> MAX takes .168ms (milliseconds, not seconds!), count() with a where
> clause takes 266ms.
Yes, sqlite is pretty quick at doing a table-scan. On the other hand, the
whole zen of database querying is about avoiding the table scan. A one-off
query may not make this obvious, but a number of queries on a much larger
database can certainly make things take longer. This is especially true if
you have a large amount of data that has to be read from disk.
> And this is is with itunes and more running at the same time on the
> machine... Maybe you didn't index the table? Indexing makes all the
> difference in the world.
Indexing won't help a COUNT(*). COUNT(*) would probably be slower if it
used an index. Instead, it uses the table's main B-tree and simply counts
all entries.
> Alle 08:54, luned́ 29 marzo 2004, Ali Sadik Kumlali ha scritto:
> > I wonder if I could use max(rowid) safely instead of count(*). Does
> > rowid increments by 1 for each new row?
> As previously stated, it's not safe.
> A workaround could be to create an additional table with one field and
> one row, in which you put the number of rows of the other table. At
> every insert or delete on the big table, you should update the value of
> this counter (the best way I can think of is using a trigger, with a
> single atomic update: UPDATE table_counter SET counter = counter + 1).
The detailed solution looks something like this:
CREATE TABLE counters (name, count);
INSERT INTO counters VALUES("foo", 0);
CREATE TRIGGER foo_insert_counter AFTER INSERT on foo
BEGIN
UPDATE counters SET count = count + 1 WHERE name = "foo";
END;
CREATE TRIGGER foo_delete_counter AFTER DELETE on foo
BEGIN
UPDATE counters SET count = count - 1 WHERE name = "foo";
END;
Benjamin.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
