[SOLVED] SQLite Table Select Query optimization

Issue

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)

Solution

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.

Answered By – MikeT

Answer Checked By – Cary Denson (BugsFixing Admin)

Leave a Reply

Your email address will not be published. Required fields are marked *