SQL BenchMEASURED, NOT GUESSED

    ↑↓ to select, Enter to open, Esc to close

    WHERE score = (indexed) is ~25.00× faster than WHERE score = (no index)

    The same statement is ~25× slower without an index on `score`: it scans all 100,000 rows.

    Result

    RunWHERE score = (indexed)WHERE score = (no index)Ratio
    Run 11.13828.7620.04×
    Run 21.19829.6350.04×
    Run 30.98624.5530.04×

    Spread over 3 runs: WHERE score = (indexed) CV 7.8% / WHERE score = (no index) CV 7.7% / Ratio CV 0.0%

    WHERE score = (indexed): 1.14msWHERE score = (no index): 28.76msRun1WHERE score = (indexed): 1.20msWHERE score = (no index): 29.64msRun2WHERE score = (indexed): 0.99msWHERE score = (no index): 24.55msRun3

    The statement

    SELECT id FROM items WHERE score = 500;
    SELECT id FROM items WHERE score = 500;  -- same query, no index on score

    Conditions

    100,000 rows, `score` is an integer between 1 and 1000.

    Where this number does not apply

    The gap disappears when the index cannot be used (function on the column, type mismatch, `OR`), and reverses when the result is a large fraction of the table.

    Reproduce

    cd bench && python sqbench.py --samples 9 --out ../data/run.json