I have a sqlite operation that stalls and then crashes my cordova app when run on a table with hundreds of rows. This is the query:
WITH
prev_types AS (
SELECT t1.*, t1.type <> COALESCE(t2.type, '') flag, MAX(t2.date) max_time
FROM chatMessages t1 LEFT JOIN chatMessages t2
ON t2.date < t1.date
GROUP BY t1.id
),
sum_flags AS (
SELECT pt1.*,
(SELECT SUM(pt2.flag) FROM prev_types pt2 WHERE pt2.date <= pt1.date) grp
FROM prev_types pt1
),
cte AS (
SELECT sf1.*,
(SELECT COUNT(*) FROM sum_flags sf2 WHERE sf2.grp = sf1.grp) count,
(SELECT COUNT(*) FROM sum_flags sf2 WHERE sf2.grp = sf1.grp AND sf2.date <= sf1.date) rn
FROM sum_flags sf1
)
SELECT id, type, date, message,
CASE WHEN type = 'groupedMsgs' THEN count END numConsecutiveItems
FROM cte
WHERE numConsecutiveItems IS NULL OR rn = 1
ORDER BY date;
It’s intended to get a count of consecutive rows of a particular type. The full context of this can be read here. Any ideas for how I can optimize this query further to make it more performant on low devices? Thanks!