I didn’t spend too much time on this issue, but as someone who knows the basics to intermediate in SQL it still caught me off-guard on how easy it is to have insanely slow query performances.
Take an innocent query like this. Once would think it has some sort of meta information stored already, but it doesn’t
select mytable.*, (select count(*) from mytable2 where mytable2.id=mytable.id ) as count from some_table;
What it does is that it will get all the columns in mytable and additionally it performs a
COUNT based on id in a subquery thus the result is the total count per ID.
It took 7662ms to perform the query on two tables that have 387, and 4,800+ rows respectively. That’s not acceptable in any way, putting that sort of query in production will make your customers leave eventually due to the slowness of the system.
I googled a bit on the issue and found that sqlite doesn’t store any meta or index. I went ahead and create an index based on the second table, as the first one already has an index (primary key).
I managed to trim those seconds to 240ms, which imho is pretty acceptable, yet makes me feel uneasy.
select mytable.*, (select count(ROWID) from mytable2 indexed by my_awesome_index where mytable2.id=mytable.id ) as count from some_table;
That’s all it took.
I’m still not completely satisfied with results like this, I will continue finding a way to trim those 200ms away to 20 or 50. I have an idea on how to do it but for now this will do.