search

simonw / covid-19-datasette

Deploys a Datasette instance of COVID-19 data from Johns Hopkins CSSE and the New York Times
https://covid-19.datasettes.com/
61 stars 6 forks source link

ny_times_us_counties needs a "date desc" index #27

Closed simonw closed 3 years ago

simonw commented 3 years ago

When you paginate through the https://covid-19.datasettes.com/covid/ny_times_us_counties table - which now has more than 1.3m records - each page takes ~200ms to query. It's sorted by date, descending with an index on date.

With an index on date desc instead each page only takes ~10ms.

simonw commented 3 years ago

Before deploying this change, https://covid-19.datasettes.com/covid?sql=select+*+from+pragma_index_xinfo%28%27idx_ny_times_us_counties_date%27%29 returns:

seqno cid name desc coll key
0 0 date 0 BINARY 1
1 -1   0 BINARY 0
simonw commented 3 years ago
And now: seqno cid name desc coll key
0 0 date 1 BINARY 1
1 -1 0 BINARY 0
simonw commented 3 years ago

Turns out that wasn't the cause of the performance problem after all! https://github.com/simonw/datasette/issues/1349