4 ms·
SQLite doesn't support indexing JSON (yet) unless you use generated columns. So performance-wise SQLite would probably not be the best option if that's all you
by karimfromjordan 2y ago
SQLite doesn't support indexing JSON (yet) unless you use generated columns. So performance-wise SQLite would probably not be the best option if that's all you do.
- TekMol 2y agoSo far, it's fast enough for me. If I ever need more performance, I guess I'll figure it out. Couldn't Indexes on Expressions be used to index JSON fields? https://www.sqlite.org/expridx.html https://www.sqlite.org/expridx.html
- KMnO4 2y agoThe “right” tool for the job depends on a number of factors. Performance is one of them, but ease of implementation is just as important depending on the scale. Since you already have it working sufficiently using SQLite, why would you switch?
- TekMol 2y agoAs I said, to get rid of the superflous ".data->>" everywhere. Take this query for example: SELECT cities.data ->> '$.name' AS city_name, countries.data ->> '$.name' AS country_name FROM cities JOIN countries ON cities.data -> '$.country_id' = countries.data -> '$.id'; It could be much smaller if the DB knew that all my tables are of the "single data column with JSON" form.
- SQLite 2y agoSQLite supports indexes on expressions. So if you have an expression that accesses some subcomponent of JSON that you want to index, just create an index on that expression.