SQLite Forum

Most performant way to test for having 0, 1 or more rows in a table
Login

Most performant way to test for having 0, 1 or more rows in a table

(1.3) By ddevienne on 2021-08-24 09:53:32 edited from 1.2 [link] [source]

Hi. We have two mutually-exclusive ON INSERT triggers on a table,
and a 3rd ON DELETE trigger.

The 1st has a WHEN (select count(*) from t) = 1,
while the 2nd has a WHEN (select count(*) from t) > 1,
and the 3rd a WHEN (select count(*) from t) = 0.

It looks simple and innocuous, but I'm worried about the performance.
Because it seems to me, this introduces quadratic behavior on inserts
and delete, from the count(*) FULL-SCANs. Is that correct?

I propose to use (select count(*) from (select 1 from t limit 2))
instead of (select count(*) from t), which I think will avoid the FULL-SCANs,
but comparing the query-plans, I don't understand the 2nd plan, so I'm not 100%
sure this is the better more performance approach, albeit more verbose.

sqlite> create table t (k integer primary key not null, data blob);
sqlite> select (select count(*) from (select 1 from t limit 2)) = 1;
QUERY PLAN
|--SCAN CONSTANT ROW
`--SCALAR SUBQUERY 2
   |--CO-ROUTINE 1
   |  `--SCAN TABLE t
   `--SCAN SUBQUERY 1
0
sqlite> select count(*) = 1 from t;
QUERY PLAN
`--SCAN TABLE t
0

In both plans, there's a SCAN TABLE t. Doesn't the LIMIT 2 avoid the full-scan, as I expect?
It's not obvious to me given the query plan output. I'm not versed in reading those I'm afraid...

If someone more knowledgeable could confirm my suspicious of the performance issue,
and confirm my proposed changes fixes it, I'd appreciate. Thanks, --DD

PS: The ON DELETE trigger can/should also use EXISTS, more idiomatic, no?

sqlite> select not exists (select 1 from t);
QUERY PLAN
|--SCAN CONSTANT ROW
`--SCALAR SUBQUERY 1
   `--SCAN TABLE t
1

(2) By David Raymond (dvdraymond) on 2021-08-24 15:20:01 in reply to 1.3 [source]

You can see the limiting in the EXPLAIN output, as it's more verbose than the shorter, more readable EXPLAIN QUERY PLAN output.

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (k integer primary key not null, data blob);
sqlite> .eqp full
sqlite> select (select count(*) from (select 1 from t limit 2)) = 1;
QUERY PLAN
|--SCAN CONSTANT ROW
`--SCALAR SUBQUERY 2
   |--CO-ROUTINE SUBQUERY 1
   |  `--SCAN t
   `--SCAN SUBQUERY 1
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     28    0                    0   Start at 28
1     Integer        22    3     0                    0   r[3]=22; return address
2     Once           0     22    0                    0
3     Null           0     4     4                    0   r[4..4]=NULL; Init subquery result
4     InitCoroutine  5     13    5                    0   SUBQUERY 1
5     Integer        2     6     0                    0   r[6]=2; LIMIT counter
6     OpenRead       1     2     0     0              0   root=2 iDb=0; t
7     Rewind         1     12    0                    0
8       Integer        1     7     0                    0   r[7]=1
9       Yield          5     0     0                    0
10      DecrJumpZero   6     12    0                    0   if (--r[6])==0 goto 12
11    Next           1     8     0                    1
12    EndCoroutine   5     0     0                    0
13    Integer        1     8     0                    0   r[8]=1; LIMIT counter
14    Null           0     9     9                    0   r[9..9]=NULL
15    InitCoroutine  5     0     5                    0
16      Yield          5     19    0                    0   next row of subquery_1
17      AggStep        0     0     9     count(0)       0   accum=r[9] step(r[0])
18    Goto           0     16    0                    0
19    AggFinal       9     0     0     count(0)       0   accum=r[9] N=0
20    Copy           9     4     0                    0   r[4]=r[9]
21    DecrJumpZero   8     22    0                    0   if (--r[8])==0 goto 22
22    Return         3     0     0                    0
23    Integer        1     1     0                    0   r[1]=1
24    Eq             10    26    4                    64  if r[4]==r[10] goto 26
25    ZeroOrNull     4     1     10                   0   r[1] = 0 OR NULL
26    ResultRow      1     1     0                    0   output=r[1]
27    Halt           0     0     0                    0
28    Transaction    0     0     1     0              1   usesStmtJournal=0
29    Integer        1     10    0                    0   r[10]=1
30    Goto           0     1     0                    0
0
sqlite>