• Jérome Perrin's avatar
    collect: small optimization on garbage collect · 686289b8
    Jérome Perrin authored
    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.
    686289b8
db.py 18.8 KB