I am not sure what is wrong with the following query.

For provided names array bigger in size/length, the query seems to run very slow (can be exponentially slow, not sure):

Here are the columns for mytable:

name, timestamp, diff, diff2, ratio, minus_one

minus_one column is just a helper column which always has a value of -1 for every inserted row.

Can someone help to optimize the query? or do you think it is fine? maybe should optimize other parts of my code to run it faster? Any suggestion is much appreciated.

Query:

SELECT 
    COUNT(diff) AS count, 
    IFNULL(AVG(diff), 0) AS avg, 
    IFNULL(MAX(diff), 0) AS max, 
    IFNULL(MIN(diff), 0) AS min 
FROM 
    mytable 
WHERE 
    name IN :names 
    AND timestamp >= :start 
    AND timestamp <= :end 
    AND ratio <> -1 
    AND (minus_one = :ratioStart OR ratio >= :ratioStart) 
    AND (minus_one = :ratioEnd OR ratio <= :ratioEnd) 
    AND (minus_one = :diffStart OR diff >= :diffStart)
    AND (minus_one = :diffEnd OR diff <= :diffEnd) 
    AND (minus_one = :diffStart2 OR diff2 >= :diffStart2) 
    AND (minus_one = :diffEnd2 OR diff2 <= :diffEnd2) 
GROUP BY 
    name 
ORDER BY 
    count DESC

CodePudding user response：

That is because the where clause takes more time to be queried. You can improve the query by doing one of the followings:

1. Using a multi-column index:

   CREATE INDEX index_name ON table(col1, col2);
   

2. Using sqflite plugin