Timeouts with the getGenres.view endpoint

[djl]

gonic version: 0.16.2
docker tag: latest

I've just started seeing timeouts on with getGenres.view endpoint. Relevant logs from Docker:

Dec 10 19:35:44 gonic.example.com docker-gonic[102189]: 2023/12/10 19:35:44 error writing subsonic response: write tcp 172.17.0.8:4747->172.17.0.1:56494: i/o timeout
Dec 10 19:35:44 gonic.example.com docker-gonic[102189]: 2023/12/10 19:35:44 response  200  GET /getGenres?u=guest&REDACTED
Dec 10 19:35:51 gonic.example.com docker-gonic[102189]: 2023/12/10 19:35:51 error writing subsonic response: write tcp 172.17.0.8:4747->172.17.0.1:56496: i/o timeout
Dec 10 19:35:51 gonic.example.com docker-gonic[102189]: 2023/12/10 19:35:51 response  200  GET /getGenres?u=guest&REDACTED

If I run the same query manually in SQLite with the timer on, it takes about nine or 10 seconds:

sqlite> .timer ON
sqlite> select *, (SELECT count(1) FROM album_genres WHERE genre_id=genres.id) album_count, (SELECT count(1) FROM track_genres WHERE genre_id=genres.id) track_count from genres group by genres.id;
... snip 500 lines ...
Run Time: real 9.001 user 8.280497 sys 0.719935

Which, if my maths is correct, is more than the default five second timeout 😄

I'm probably an outlier here with ~500 genres but it feels like this query shouldn't take nearly as long as it does. Am I wrong in that assumption?

[sentriz]

oh you're right. it's doing 3 full table scans and not using any indexes seemingly.. strange

maybe it's because some of the indexes are compound indexes, where the column we're querying on doesn't come first

[sentriz]

its almost instant when adding two more indexes. but seems like we have duplicate indexes now. the compound and the single. need to investiate more on how that works

[sentriz]

should be fixed on :nightly

[djl]

Working great on nightly. Thanks!