SQL BenchMEASURED, NOT GUESSED

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

    LIKE 'item-0123%' (prefix) is ~1.11× faster than LIKE '%12345%' (contains)

    Prefix vs contains only differs by ~11% here, because `LIMIT 200` stops both scans early. The lesson is that `LIMIT` matters more than the LIKE pattern in this shape of query.

    Result

    RunLIKE 'item-0123%' (prefix)LIKE '%12345%' (contains)Ratio
    Run 113.38615.1670.88×
    Run 217.57318.4280.95×
    Run 312.48813.9060.90×

    Spread over 3 runs: LIKE 'item-0123%' (prefix) CV 16.6% / LIKE '%12345%' (contains) CV 12.6% / Ratio CV 3.4%

    LIKE 'item-0123%' (prefix): 13.39msLIKE '%12345%' (contains): 15.17msRun1LIKE 'item-0123%' (prefix): 17.57msLIKE '%12345%' (contains): 18.43msRun2LIKE 'item-0123%' (prefix): 12.49msLIKE '%12345%' (contains): 13.91msRun3

    The statement

    SELECT id FROM items WHERE name LIKE 'item-0123%' LIMIT 200;
    SELECT id FROM items WHERE name LIKE '%12345%' LIMIT 200;

    Conditions

    100,000 rows, `name` is `item-000001` style, indexed.

    Where this number does not apply

    Without `LIMIT`, or when few rows match, the prefix form can use the index and the gap widens.

    Reproduce

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