[yunqa.de] Re: max(rowid) instead of Count(*) for large databases?
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Tue, 11 Dec 2012 15:31:38 +0100
On 11.12.2012 08:43, email@xxxxxxxxxx wrote:
> I've been trying to optimize my application to work with rather large
> databases, and I accidentally realized that max(rowid) is way faster
> than Count(*)
>
> Assuming I don't delete records from database, is it safe to use this
> SQL statement?
>
> SELECT MAX(rowid) from MyTable
>
> instead of
>
> SELECT Count(*) from MyTable
max(rowid) is fast because it uses an indexed scan. count(*) is slow
because it requires a full table scan.
SQLite is not specific about how it assigns rowids. As implemented now,
it increments rowids for additional records. So if no records are
deleted, count(*) = max(rowid) is usually correct.
I write usually because SQLite does not guarantee that this is the case,
not now and not in the future. It may change with the next version.
Hence I do not recommend it. If you use it, use it at your own risk. You
have been warned!
The recommended way is to use triggers to track the table count. The
triggers will slow down INSERTs and DELETEs slightly but retrieving the
count is instantaneous, regardless of table size.
Here is some example SQL:
-- This table stores the other table's counts.
CREATE TABLE Counts (t INTEGER);
INSERT INTO Counts VALUES (0);
-- Create a data table.
CREATE TABLE t (a);
-- Add INSERT and DELETE triggers for table t.
CREATE TRIGGER t_ins AFTER INSERT ON t
BEGIN
UPDATE Counts SET t = t + 1;
END;
CREATE TRIGGER t_del AFTER DELETE ON t
BEGIN
UPDATE Counts SET t = t - 1;
END;
-- INSERT and DELETE some data to test.
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (3);
INSERT INTO t VALUES (4);
DELETE FROM t WHERE a = 1;
-- Retrieve the count.
SELECT
(SELECT count(*) FROM t), -- Conventional, slow.
(SELECT t FROM Counts); -- Counts table, fast.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
