Panel feels sluggish once the SQLite gets big

[tobimori]

Once the records table grows past a few-thousand rows, the 404 and statistics panels take hundreds of ms (sometimes seconds) to load.

Running EXPLAIN QUERY PLAN reveals that every range filter is performing a full table scan:

# open the log file
sqlite3 site/logs/retour/log.sqlite

# check the plan for fails()
EXPLAIN QUERY PLAN
SELECT path
FROM records
WHERE strftime('%s', date) >= strftime('%s', '2025-01-01')
  AND redirect IS NULL
LIMIT 1;

Result → SCAN TABLE records (no index).

Why this happens

• strftime() in all range filters: wrapping date disables any chance of using an index, even though the column is stored as sortable YYYY-MM-DD HH:MM:SS text.
• No indexes shipped with assets/retour.sqlite, so even equality look-ups walk the whole table.

How I solved it:

1. Remove strftime()

- ->andWhere('strftime("%s", date) >= strftime("%s", :start)', ['start' => $from])
- ->andWhere('strftime("%s", date) <= strftime("%s", :end)',   ['end'   => $to])
+ ->andWhere('date >= :start', ['start' => $from])
+ ->andWhere('date <= :end',   ['end'   => $to])

Apply the same change in fails(), redirect(), purge(), and the raw SQL inside stats().

2. Add the indices

CREATE INDEX IF NOT EXISTS idx_redirect_date ON records (redirect, date);
CREATE INDEX IF NOT EXISTS idx_path          ON records (path);
CREATE INDEX IF NOT EXISTS idx_fails         ON records (redirect, wasResolved, date);

For my DB with about 2 million entries, this made the queries go from 30+ secs to 6,5 secs.

This could be worth exploring

• fails() – switch COUNT(date) → COUNT(*) as well

• stats() – use single-pass conditional aggregates instead of arithmetic on multiple COUNT() calls:

  SUM(CASE WHEN redirect IS NULL  AND wasResolved IS NULL  THEN 1 END) AS failed,
  SUM(CASE WHEN wasResolved IS NOT NULL AND redirect IS NULL THEN 1 END) AS resolved,
  SUM(CASE WHEN redirect IS NOT NULL                      THEN 1 END) AS redirected
  

Maybe some database expert has some more ideas, but this already helped tons!

[distantnative]

I finally want to tackle this. But contemplating what would be a good upgrade path with this (considering that existing DBs will not have the indices. Or ignore that and just optimize for newly created DBs?

[tobimori]

Indices will increase file size and insert performance. Maybe supply a CLI command to upgrade manually?

[distantnative]

@tobimori Do you know how much the file size changed for your 2 mio entries DB (I know, asking half a year later)? I would imagine file size might be not as considerable as performance gains.

CLI command could be an option.

[tobimori]

I think like 1,5-2x but dunno really