What do the indexes buy?

• Wing scans of one ticket

  Searches wing_scan_ticket

  Speed-up (median ratio, 95% CI)

  42× [39×, 44×]

  Without indexes

  0.417 ms0.412 ms to 0.425 ms

  With indexes

  9.9 µs9.6 µs to 0.011 ms

  Same rows: yes

• Tickets in one order

  Searches ticket_order

  Speed-up (median ratio, 95% CI)

  27× [26×, 28×]

  Without indexes

  0.214 ms0.213 ms to 0.219 ms

  With indexes

  7.9 µs7.8 µs to 8.2 µs

  Same rows: yes

• Audio-guide hires on one day, as a range

  Searches audio_guide_hire_issued

  Speed-up (median ratio, 95% CI)

  5.6× [5.4×, 5.7×]

  Without indexes

  0.049 ms0.049 ms to 0.050 ms

  With indexes

  8.7 µs8.6 µs to 9.1 µs

  Same rows: yes

• The same day, written with date()

  Scans audio_guide_hire_device end to end

  Speed-up (median ratio, 95% CI)

  1.0× [1.0×, 1.0×]: no practical difference

  Without indexes

  0.104 ms0.102 ms to 0.105 ms

  With indexes

  0.102 ms0.102 ms to 0.104 ms

  Same rows: yes

• History of one audio-guide device

  Searches audio_guide_hire_device, sqlite_autoindex_audio_guide_device_1

  Speed-up (median ratio, 95% CI)

  4.7× [4.6×, 4.8×]

  Without indexes

  0.058 ms0.058 ms to 0.059 ms

  With indexes

  0.013 ms0.012 ms to 0.013 ms

  Same rows: yes

• Entries on one day (proposed index)

  Searches entry_scan_scanned_at

  Speed-up (median ratio, 95% CI)

  42× [41×, 42×]

  Without indexes

  0.347 ms0.343 ms to 0.352 ms

  With indexes

  8.3 µs8.2 µs to 8.4 µs

  Same rows: yes

• Most common wing routes (business question 5)

  Scans wing_scan_ticket end to end

  Speed-up (median ratio, 95% CI)

  0.8× [0.8×, 0.9×]: the indexed copy is slower

  Without indexes

  15.7 ms15.5 ms to 15.8 ms

  With indexes

  18.5 ms18.4 ms to 18.6 ms

  Same rows: yes

• Lookups are where indexes pay

  Finding one ticket's wing scans or one order's tickets reads a handful of index entries instead of every row: tens of times faster, with intervals far from 1×.

• How a query is written decides whether an index can help

  The same day's hires, filtered with date(issued_at) = … as business question 4 does, cannot use the index on issued_at; written as a range, it can.

• An index can make a query slower

  The whole-table route query reads every wing scan either way. With the index present, SQLite walks it to avoid a sort and pays for a lookup per row, so the indexed copy is the slower one. Reported as measured.

• A proposed index, tested before it is adopted

  Daily and hourly entry counts filter entry scans by time; the shipped schema has no index for that. It is not in the shipped schema; this experiment is the evidence for adding it.

1. Wing scans of one ticket

   Where did ticket 4321 go inside the museum? A point lookup on the largest table (19,545 rows): the index on wing_scan(ticket_id) turns a full scan into a search.

   SELECT wing_id, scanned_at FROM wing_scan WHERE ticket_id = 4321 ORDER BY scanned_at

   Plan without indexes

   SCAN wing_scan
   USE TEMP B-TREE FOR ORDER BY

   Plan with indexes

   SEARCH wing_scan USING INDEX wing_scan_ticket (ticket_id=?)
   USE TEMP B-TREE FOR ORDER BY

   1 row; both copies returned the same rows: yes. Each sample ran the query 6 times (without) and 214 times (with).

2. Tickets in one order

   Which tickets did order 2500 buy? The index on ticket(order_id) serves the most common join in the schema.

   SELECT ticket_id, barcode FROM ticket WHERE order_id = 2500

   Plan without indexes

   SCAN ticket

   Plan with indexes

   SEARCH ticket USING INDEX ticket_order (order_id=?)

   2 rows; both copies returned the same rows: yes. Each sample ran the query 19 times (without) and 425 times (with).

3. Audio-guide hires on one day, as a range

   Which guides went out on 6 August 2019? Written as a range on the raw column, the index on audio_guide_hire(issued_at) can be used.

   SELECT serial_number, issued_at FROM audio_guide_hire WHERE issued_at >= '2019-08-06' AND issued_at < '2019-08-07'

   Plan without indexes

   SCAN audio_guide_hire

   Plan with indexes

   SEARCH audio_guide_hire USING INDEX audio_guide_hire_issued (issued_at>? AND issued_at<?)

   5 rows; both copies returned the same rows: yes. Each sample ran the query 64 times (without) and 409 times (with).

4. The same day, written with date()

   The same question as business question 4 writes it. Wrapping the column in date() hides it from the index, so both copies scan the whole table: the index cannot help a query written this way.

   SELECT serial_number, issued_at FROM audio_guide_hire WHERE date(issued_at) = '2019-08-06'

   Plan without indexes

   SCAN audio_guide_hire

   Plan with indexes

   SCAN audio_guide_hire USING COVERING INDEX audio_guide_hire_device

   5 rows; both copies returned the same rows: yes. Each sample ran the query 26 times (without) and 28 times (with).

5. History of one audio-guide device

   When was one device hired, in order? The composite index on (serial_number, issued_at) answers the filter and the sort together; it also backs the trigger that stops double hires.

   SELECT hire_id, issued_at, returned_at FROM audio_guide_hire WHERE serial_number = (SELECT min(serial_number) FROM audio_guide_device) ORDER BY issued_at

   Plan without indexes

   SCAN audio_guide_hire
   SCALAR SUBQUERY 1
     SEARCH audio_guide_device USING COVERING INDEX sqlite_autoindex_audio_guide_device_1
   USE TEMP B-TREE FOR ORDER BY

   Plan with indexes

   SEARCH audio_guide_hire USING INDEX audio_guide_hire_device (serial_number=?)
   SCALAR SUBQUERY 1
     SEARCH audio_guide_device USING COVERING INDEX sqlite_autoindex_audio_guide_device_1

   9 rows; both copies returned the same rows: yes. Each sample ran the query 65 times (without) and 275 times (with).

6. Entries on one day (proposed index)

   How many entrance scans were there on 6 August 2019? Only the proposed index on entry_scan(scanned_at) can help here; it is not in the shipped schema.

   SELECT count(*) FROM entry_scan WHERE scanned_at >= '2019-08-06' AND scanned_at < '2019-08-07'

   Plan without indexes

   SCAN entry_scan

   Plan with indexes

   SEARCH entry_scan USING COVERING INDEX entry_scan_scanned_at (scanned_at>? AND scanned_at<?)

   1 row; both copies returned the same rows: yes. Each sample ran the query 12 times (without) and 429 times (with).

7. Most common wing routes (business question 5)

   In what order do visitors tour the wings? A whole-table aggregation reads every row either way; an index should make little or no difference.

   WITH scans AS (
     SELECT ticket_id, scanned_at, scan_id, wing_id,
            lag(wing_id) OVER (PARTITION BY ticket_id ORDER BY scanned_at, scan_id) AS previous_wing
     FROM wing_scan
   )
   SELECT count(*) FROM scans WHERE previous_wing IS NULL OR previous_wing <> wing_id

   Plan without indexes

   CO-ROUTINE scans
     CO-ROUTINE (subquery-3)
       SCAN wing_scan
       USE TEMP B-TREE FOR ORDER BY
     SCAN (subquery-3)
   SCAN scans

   Plan with indexes

   CO-ROUTINE scans
     CO-ROUTINE (subquery-3)
       SCAN wing_scan USING INDEX wing_scan_ticket
       USE TEMP B-TREE FOR LAST 2 TERMS OF ORDER BY
     SCAN (subquery-3)
   SCAN scans

   1 row; both copies returned the same rows: yes. Each sample ran the query 1 time (without) and 1 time (with).

Indexes dropped for the “without” copy: audio_guide_hire_device, audio_guide_hire_issued, audio_guide_hire_ticket, entry_scan_one_first_entry, exhibition_booking_ticket, ticket_order, wing_scan_ticket, entry_scan_scanned_at. SQLite's automatic indexes (behind primary keys and UNIQUE constraints) cannot be dropped and stay in both.