SQL BenchMEASURED, NOT GUESSED

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

    ORDER BY score (indexed) is ~29.41× faster than ORDER BY payload (no index)

    Sorting on an indexed column and taking 50 rows is ~30× faster; the unindexed version reads and sorts everything.

    Result

    RunORDER BY score (indexed)ORDER BY payload (no index)Ratio
    Run 11.09732.8130.03×
    Run 21.14633.7460.03×
    Run 31.05129.0160.04×

    Spread over 3 runs: ORDER BY score (indexed) CV 3.5% / ORDER BY payload (no index) CV 6.2% / Ratio CV 3.7%

    ORDER BY score (indexed): 1.10msORDER BY payload (no index): 32.81msRun1ORDER BY score (indexed): 1.15msORDER BY payload (no index): 33.75msRun2ORDER BY score (indexed): 1.05msORDER BY payload (no index): 29.02msRun3

    The statement

    SELECT id, score FROM items ORDER BY score LIMIT 50;
    SELECT id, payload FROM items ORDER BY payload LIMIT 50;

    Conditions

    100,000 rows; `payload` is the same 40-character string in every row so the sort is deterministic.

    Where this number does not apply

    If you return most of the table, or filter on another column, the index does not help.

    Reproduce

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