GitLab

This changes a query like:

SELECT date FROM user WHERE reported = 1 AND date != '2020-04-13' AND date != '2020-04-12'  AND date != '2020-04-11'  AND date != '2020-04-10'  AND date != '2020-04-09'  AND date != '2020-04-08'  AND date != '2020-04-07'  AND date != '2020-04-06'  AND date != '2020-04-05' AND date != '2020-04-04'  AND date != '2020-04-03'  AND date != '2020-04-02'  AND date != '2020-04-01'  AND date != '2020-03-31'  AND date != '2020-03-30'  LIMIT 1

EXPLAIN QUERY PLAN ...
0|0|0|SCAN TABLE user USING COVERING INDEX user_date_reported_index

which took ~3 seconds on a 1.5Go collector.db into:

SELECT date FROM user WHERE reported = 1 AND (date < '2020-03-30'  OR  date > '2020-04-13')  LIMIT 1

EXPLAIN QUERY PLAN ...
0|0|0|SEARCH TABLE user USING COVERING INDEX user_date_reported_index (date<?)
0|0|0|SEARCH TABLE user USING COVERING INDEX user_date_reported_index (date>?)

which is instant.