3 ms·
The query engine in SQLite can be weak. In particular where JOINs across multiple columns are concerned. You really do need to be aware of this when designing
by akira2501 2y ago
The query engine in SQLite can be weak. In particular where JOINs across multiple columns are concerned. You really do need to be aware of this when designing indexes into SQLite files.
In any case, SQLite would serve this solution, but almost certainly with a measurable level of inefficiency built in.
- metadat 2y agoThis is the first I've heard of SQLite JOIN performance being "weak". I just spent 10 minutes scouring the annals of the web and didn't turn up anything relevant. Are there any additional links or other information you can share about this claim? Edit: @akira2501: SQLite comes with different tradeoffs, sometimes superior and other times less so, depending on the use case. Blanket statements without evidence are unproductive in progressing the conversation..
- akira2501 2y agoSeriously? https://www.sqlite.org/optoverview.html https://www.sqlite.org/optoverview.html Chapter 2. Chapter 7. Chapter 8. It should be _zero_ surprise to you that SQLite is not as powerful as other full database query engines.
- simonw 2y agoI don’t understand how reading that documentation page makes it clear that other relational engines have better performance for joins.
- anacrolix 2y agoI'm with you. I've encountered numerous situations where the query planner caused issues. I don't know if the alternatives are better, but it's definitely an issue. I've written multiple databases wrapping SQLite, and multiple bindings to SQLite. There are dragons.
- vrighter 2y agoI had to use sqlite once for something that would do a ton of read queries on a particular table. Sometimes just swapping the order of operands to an and operation generated a completely different query plan. Had to scour their optimizer document and painstakingly check the query plans of, and then manually tune, each sql statement. But then it was quite fast.
- rizky05 2y ago[dead]
- deleted 2y ago[deleted]
- Groxx 2y agoWeeellll... I think it's safe to say SQLite's planner is simple, and trends towards "you get what you wrote" rather than having more sophisticated runtime monitoring and adjusting. But as with all things SQLite, the answer is generally "it's easy to predict so just improve your query" and/or "there's a plugin for that" so it tends to win in the end with a bit of care.
- thesz 2y agohttps://www.sqlite.org/queryplanner-ng.html https://www.sqlite.org/queryplanner-ng.html SQLite's planner is anything but simple. It has a beam search over different plans, basically, it approximates full NP-hard solution search process using bounded space and time.