SQL query is too slow (flutter mobile)
I am not sure what is wrong with the following query.
names array bigger in size/length, the query seems to run very slow (can be exponentially slow, not sure):
Here are the columns for
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.
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
That is because the where clause takes more time to be queried. You can improve the query by doing one of the followings:
Using a multi-column index:
CREATE INDEX index_name ON table(col1, col2);
Using sqflite plugin