I don't know how critical is the performance of the following SQL query, or how many times it is used, but seeing an opportunity to improve I'm jumping in.
In function gravity_Table_Count()
in gravity.sh there's the query:
SELECT COUNT(*) FROM (SELECT DISTINCT domain FROM ${table});
SQL has the operator COUNT (DISTINCT column)
, therefore the above query can be rewritten as
SELECT COUNT(DISTINCT domain) FROM ${table};
It makes a difference for SQLite. If you compare the output of EXPLAIN
(the first one below is optimized, the second one is original), you'll see the execution plan for the optimized version is shorter. For ${tables} with substantial number of rows it can make a real difference.
I hope it's useful and will make Pi-Hole a little faster for users
sqlite> create table t1(c1 int);
sqlite> explain select count(distinct c1) from t1;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 15 0 0 Start at 15
1 Null 0 1 2 0 r[1..2]=NULL
2 OpenEphemeral 1 0 0 k(1,B) 0 nColumn=0
3 OpenRead 0 2 0 1 0 root=2 iDb=0; t1
4 Rewind 0 11 0 0
5 Column 0 0 3 0 r[3]= cursor 0 column 0
6 Found 1 10 3 1 0 key=r[3]
7 MakeRecord 3 1 4 0 r[4]=mkrec(r[3])
8 IdxInsert 1 4 3 1 16 key=r[4]
9 AggStep 0 3 2 count(1) 1 accum=r[2] step(r[3])
10 Next 0 5 0 1
11 AggFinal 2 1 0 count(1) 0 accum=r[2] N=1
12 Copy 2 5 0 0 r[5]=r[2]
13 ResultRow 5 1 0 0 output=r[5]
14 Halt 0 0 0 0
15 Transaction 0 0 1 0 1 usesStmtJournal=0
16 Goto 0 1 0 0
sqlite> explain select count(*) from (select distinct(c1) from t1) x;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 21 0 0 Start at 21
1 InitCoroutine 1 12 2 0 x
2 OpenEphemeral 2 0 0 k(1,B) 8 nColumn=0
3 OpenRead 1 2 0 1 0 root=2 iDb=0; t1
4 Rewind 1 11 0 0
5 Column 1 0 2 0 r[2]= cursor 1 column 0
6 Found 2 10 2 1 0 key=r[2]
7 MakeRecord 2 1 3 0 r[3]=mkrec(r[2])
8 IdxInsert 2 3 2 1 16 key=r[3]
9 Yield 1 0 0 0
10 Next 1 5 0 1
11 EndCoroutine 1 0 0 0
12 Null 0 4 4 0 r[4..4]=NULL
13 InitCoroutine 1 0 2 0
14 Yield 1 17 0 0 next row of x
15 AggStep 0 0 4 count(0) 0 accum=r[4] step(r[0])
16 Goto 0 14 0 0
17 AggFinal 4 0 0 count(0) 0 accum=r[4] N=0
18 Copy 4 5 0 0 r[5]=r[4]
19 ResultRow 5 1 0 0 output=r[5]
20 Halt 0 0 0 0
21 Transaction 0 0 1 0 1 usesStmtJournal=0
22 Goto 0 1 0 0