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
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