SQLite Table Select Query optimization
In my SQLite table data will be like this
UNIQUE_DATA_ID, DET_ID_1, DET_ID_2, DET_ID_3, DET_ID_4, DET_ID_5, DET_ID_6, DET_ID_7, DET_ID_8, DET_ID_9, DET_ID_10, DET_ID_11, DET_ID_12
[1, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
[2, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
[3, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
[4, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
[5, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
[6, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
[7, 178, 6003041, -1, 606245, 6006919, 6007, 600113, -1, 600011, -1, 6013, -1]
I need to get a single row that matches the criteria irrespective of UNIQUE_DATA_ID . in the above table only UNIQUE_DATA_ID varies for each row.
this is my query
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
If I run this I am getting all the values from the above table and it's a little bit fast (1ms) when compared to a query with a limit.
I need to get only one row so I used a limit of 1
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011 LIMIT 1
But this takes 5ms time.
In this table only UNIQUE_DATA_ID is indexed. This UNIQUE_DATA_ID is varied for each row because it's coming from another table.
In which way I can optimize this select query and get exactly one row.
and also what about inner query after indexing DET_ID_1 like this
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where DET_ID_1= (select distinct(DET_ID_1) from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011)
Are you sure that your observations are correct?
The following, based upon the information provided in your question, returns results that consistently show that for 999999 rows the query with LIMIT 1 significantly reduces the elapsed time. Obviously such times are device dependant.
With LIMIT 1 the query takes up to 2 milliseconds as opposed to over 4seconds without the LIMIT.
However, a run (this run having UNIQUE_DATA_ID as the primary key, as opposed to an index (times about the same)). The message log (from Navicat for SQlite):-
DROP TABLE IF EXISTS `354567000013_6744043_DET_TABLE`
> OK
> Time: 4.592s
CREATE TABLE IF NOT EXISTS `354567000013_6744043_DET_TABLE` (UNIQUE_DATA_ID INTEGER PRIMARY KEY,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12)
> OK
> Time: 0.094s
-- CREATE UNIQUE INDEX idx_UDI ON `354567000013_6744043_DET_TABLE`(UNIQUE_DATA_ID);
WITH cte(counter) AS (SELECT 1 UNION ALL SELECT counter+1 FROM cte LIMIT 999999)
INSERT INTO `354567000013_6744043_DET_TABLE`
(UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12)
SELECT counter,178,-6003041,-1,606245,6006919,6007,600113,-1,600011,-1,6013,-1
FROM cte
> Affected rows: 999999
> Time: 2.661s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
LIMIT 1
> OK
> Time: 0.002s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
> OK
> Time: 4.507s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
> OK
> Time: 4.107s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
LIMIT 1
> OK
> Time: 0s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
> OK
> Time: 4.241s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011
LIMIT 1
> OK
> Time: 0s
If, as per the edited, question you use :-
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where DET_ID_1= (select distinct(DET_ID_1) from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011)
Then the results show a slight improvement to just under 4 secs (again LIMIT significantly reduces the time):-
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where DET_ID_1= (select distinct(DET_ID_1) from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011)
LIMIT 1
> OK
> Time: 0s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where DET_ID_1= (select distinct(DET_ID_1) from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011)
> OK
> Time: 3.919s
EXPLAIN QUERY PLAN
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where DET_ID_1= (select distinct(DET_ID_1) from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011)
> OK
> Time: 0s
select UNIQUE_DATA_ID,DET_ID_1,DET_ID_2,DET_ID_3,DET_ID_4,DET_ID_5,DET_ID_6,DET_ID_7,DET_ID_8,DET_ID_9,DET_ID_10,DET_ID_11,DET_ID_12 from `354567000013_6744043_DET_TABLE`
where DET_ID_1= (select distinct(DET_ID_1) from `354567000013_6744043_DET_TABLE`
where
DET_ID_2=-6003041
AND DET_ID_3=-1
AND DET_ID_4=606245
AND DET_ID_5=6006919
AND DET_ID_6=6007
AND DET_ID_7=600113
AND DET_ID_8=-1
AND DET_ID_9=600011)
LIMIT 1
> OK
> Time: 0s
Note the EXPLAIN QUERY PLAN this results in :-
id parent notused detail
2 0 0 SCAN TABLE 354567000013_6744043_DET_TABLE
7 0 0 SCALAR SUBQUERY 1
12 7 0 SCAN TABLE 354567000013_6744043_DET_TABLE
36 7 0 USE TEMP B-TREE FOR DISTINCT
As opposed to the original query giving:-
id parent notused detail
2 0 0 SCAN TABLE 354567000013_6744043_DET_TABLE
You can also use EXPLAIN
for the query with the subquery/distinct the result is:-
addr opcode p1 p2 p3 p4 p5 comment
0 Init 0 51 0 00 Start at 51
1 OpenRead 0 2 0 13 00 root=2 iDb=0; 354567000013_6744043_DET_TABLE
2 Rewind 0 50 0 00
3 Column 0 1 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_1
4 Integer 33 3 0 00 r[3]=33; return address
5 Once 0 33 0 00
6 Null 0 4 4 00 r[4..4]=NULL; Init subquery result
7 Integer 1 5 0 00 r[5]=1; LIMIT counter
8 OpenEphemeral 2 0 0 k(1,B) 08 nColumn=0
9 OpenRead 1 2 0 10 00 root=2 iDb=0; 354567000013_6744043_DET_TABLE
10 Rewind 1 33 0 00
11 Column 1 2 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_2
12 Ne 7 32 6 (BINARY) 51 if r[6]!=r[7] goto 32
13 Column 1 3 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_3
14 Ne 8 32 6 (BINARY) 51 if r[6]!=r[8] goto 32
15 Column 1 4 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_4
16 Ne 9 32 6 (BINARY) 51 if r[6]!=r[9] goto 32
17 Column 1 5 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_5
18 Ne 10 32 6 (BINARY) 51 if r[6]!=r[10] goto 32
19 Column 1 6 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_6
20 Ne 11 32 6 (BINARY) 51 if r[6]!=r[11] goto 32
21 Column 1 7 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_7
22 Ne 12 32 6 (BINARY) 51 if r[6]!=r[12] goto 32
23 Column 1 8 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_8
24 Ne 8 32 6 (BINARY) 51 if r[6]!=r[8] goto 32
25 Column 1 9 6 00 r[6]=354567000013_6744043_DET_TABLE.DET_ID_9
26 Ne 13 32 6 (BINARY) 51 if r[6]!=r[13] goto 32
27 Column 1 1 4 00 r[4]=354567000013_6744043_DET_TABLE.DET_ID_1
28 Found 2 32 4 1 00 key=r[4]
29 MakeRecord 4 1 6 00 r[6]=mkrec(r[4])
30 IdxInsert 2 6 4 1 10 key=r[6]
31 DecrJumpZero 5 33 0 00 if (--r[5])==0 goto 33
32 Next 1 11 0 01
33 Return 3 0 0 00
34 Ne 4 49 1 (BINARY) 51 if r[1]!=r[4] goto 49
35 Rowid 0 14 0 00 r[14]=rowid
36 Column 0 1 15 00 r[15]=354567000013_6744043_DET_TABLE.DET_ID_1
37 Column 0 2 16 00 r[16]=354567000013_6744043_DET_TABLE.DET_ID_2
38 Column 0 3 17 00 r[17]=354567000013_6744043_DET_TABLE.DET_ID_3
39 Column 0 4 18 00 r[18]=354567000013_6744043_DET_TABLE.DET_ID_4
40 Column 0 5 19 00 r[19]=354567000013_6744043_DET_TABLE.DET_ID_5
41 Column 0 6 20 00 r[20]=354567000013_6744043_DET_TABLE.DET_ID_6
42 Column 0 7 21 00 r[21]=354567000013_6744043_DET_TABLE.DET_ID_7
43 Column 0 8 22 00 r[22]=354567000013_6744043_DET_TABLE.DET_ID_8
44 Column 0 9 23 00 r[23]=354567000013_6744043_DET_TABLE.DET_ID_9
45 Column 0 10 24 00 r[24]=354567000013_6744043_DET_TABLE.DET_ID_10
46 Column 0 11 25 00 r[25]=354567000013_6744043_DET_TABLE.DET_ID_11
47 Column 0 12 26 00 r[26]=354567000013_6744043_DET_TABLE.DET_ID_12
48 ResultRow 14 13 0 00 output=r[14..26]
49 Next 0 3 0 01
50 Halt 0 0 0 00
51 Transaction 0 0 13241 0 01 usesStmtJournal=0
52 Integer -6003041 7 0 00 r[7]=-6003041
53 Integer -1 8 0 00 r[8]=-1
54 Integer 606245 9 0 00 r[9]=606245
55 Integer 6006919 10 0 00 r[10]=6006919
56 Integer 6007 11 0 00 r[11]=6007
57 Integer 600113 12 0 00 r[12]=600113
58 Integer 600011 13 0 00 r[13]=600011
59 Goto 0 1 0 00
As opposed to :-
addr opcode p1 p2 p3 p4 p5 comment
0 Init 0 35 0 00 Start at 35
1 OpenRead 0 2 0 13 00 root=2 iDb=0; 354567000013_6744043_DET_TABLE
2 Rewind 0 34 0 00
3 Column 0 2 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_2
4 Ne 2 33 1 (BINARY) 51 if r[1]!=r[2] goto 33
5 Column 0 3 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_3
6 Ne 3 33 1 (BINARY) 51 if r[1]!=r[3] goto 33
7 Column 0 4 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_4
8 Ne 4 33 1 (BINARY) 51 if r[1]!=r[4] goto 33
9 Column 0 5 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_5
10 Ne 5 33 1 (BINARY) 51 if r[1]!=r[5] goto 33
11 Column 0 6 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_6
12 Ne 6 33 1 (BINARY) 51 if r[1]!=r[6] goto 33
13 Column 0 7 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_7
14 Ne 7 33 1 (BINARY) 51 if r[1]!=r[7] goto 33
15 Column 0 8 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_8
16 Ne 3 33 1 (BINARY) 51 if r[1]!=r[3] goto 33
17 Column 0 9 1 00 r[1]=354567000013_6744043_DET_TABLE.DET_ID_9
18 Ne 8 33 1 (BINARY) 51 if r[1]!=r[8] goto 33
19 Rowid 0 9 0 00 r[9]=rowid
20 Column 0 1 10 00 r[10]=354567000013_6744043_DET_TABLE.DET_ID_1
21 Column 0 2 11 00 r[11]=354567000013_6744043_DET_TABLE.DET_ID_2
22 Column 0 3 12 00 r[12]=354567000013_6744043_DET_TABLE.DET_ID_3
23 Column 0 4 13 00 r[13]=354567000013_6744043_DET_TABLE.DET_ID_4
24 Column 0 5 14 00 r[14]=354567000013_6744043_DET_TABLE.DET_ID_5
25 Column 0 6 15 00 r[15]=354567000013_6744043_DET_TABLE.DET_ID_6
26 Column 0 7 16 00 r[16]=354567000013_6744043_DET_TABLE.DET_ID_7
27 Column 0 8 17 00 r[17]=354567000013_6744043_DET_TABLE.DET_ID_8
28 Column 0 9 18 00 r[18]=354567000013_6744043_DET_TABLE.DET_ID_9
29 Column 0 10 19 00 r[19]=354567000013_6744043_DET_TABLE.DET_ID_10
30 Column 0 11 20 00 r[20]=354567000013_6744043_DET_TABLE.DET_ID_11
31 Column 0 12 21 00 r[21]=354567000013_6744043_DET_TABLE.DET_ID_12
32 ResultRow 9 13 0 00 output=r[9..21]
33 Next 0 3 0 01
34 Halt 0 0 0 00
35 Transaction 0 0 13241 0 01 usesStmtJournal=0
36 Integer -6003041 2 0 00 r[2]=-6003041
37 Integer -1 3 0 00 r[3]=-1
38 Integer 606245 4 0 00 r[4]=606245
39 Integer 6006919 5 0 00 r[5]=6006919
40 Integer 6007 6 0 00 r[6]=6007
41 Integer 600113 7 0 00 r[7]=600113
42 Integer 600011 8 0 00 r[8]=600011
43 Goto 0 1 0 00
The above is covered in The SQLite Bytecode Engine, the link for EXPLAIN QUERY PLAN also includes useful links to such as the one to The Next Generation Query Planner, which explains much about query optimisation. This additionally has links you may wish to consider the links in the 4th paragraph.