alt Hacker News> Maybe one day I’ll learn to read a query plan.
With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
sqlite> CREATE TABLE x1(a, b, c); -- Create table in database
sqlite> .expert
sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Analyze this SELECT
CREATE INDEX x1_idx_000123a7 ON x1(a, b);
0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (a=? AND b>?)
sqlite> CREATE INDEX x1ab ON x1(a, b); -- Create the recommended index
sqlite> .expert
sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Re-analyze the same SELECT
(no new indexes)
0|0|0|SEARCH TABLE x1 USING INDEX x1ab (a=? AND b>?)
> My approach so far has been to just do these cleanup operations in small batches so that I don’t need to do database queries that take more than 5 seconds to run. This whole experience has given me more of an appreciation for why someone might want to use a “real” database like Postgres which can have more than one writer at the same time though.
The advice for those " “real” " databases is generally to also do cleanup operations in small batches, they just tend to make it less obvious you're doing something unperformant in the smaller case. You're more right than you thought!
Replies
> they just tend to make it less obvious you're doing something unperformant
Is this being positioned as a strength, in your comment?
I've worked with large MySQL databases that used row-based replication and things like an UPDATE or DELETE that affected millions of rows had to be applied in batches there, because otherwise one SQL query might result in a million updated rows needing to be sent to all of the replicas at once.