Kind of makes absolutely no sense other than you having to pay extra for a worker, on top of DB costs.
Iwhy we cant just have normal sql connectionsD1 is built on top of SQLite. There is no "normal SQL connections" because unlike Postgres/MySQL/SQL Server, SQLite DBs are normally used locally and have no connection protocol or way to connect remotely.
DDI
LR
LR
CLR
X
•4/1/24, 1:30 PM
L
L
L
BBLCombined with db round trips (even with smart placement) being consistently high latency, has led us to have to move to Postgres. We notice that “cold start” times and round trips are consistently better from across the globe, even with a single Postgres instance in us-east :/
M
wrangler d1 insights into the dash)
L
SM
O
DD
AO
SELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY created DESC LIMIT ?3 OFFSET ?4 userId and tagLIMIT was 100 I would expect 100 rows_read without the order, but with the order it shows every object in the tag x 2 (i.e. 600 rows_read for 300 existing objects with the tag).... Any way to optimize this? Or another way to order it in descending order without reading the entire index?M
DLFor example if my LIMIT was 100 I would expect 100 rows_read without the order,That's not correct. SQLite has to read rows of its indexes as well to figure out which rows you want. It's very rare that your
LIMIT will be the total number of rows the actual SQLite query will process. Keep in mind, indexes do count as rows read/written themselves too.EXPLAIN and see what SQLite tells you will do, you can do this in the D1 Console UI too. This is the most reliable way to see what index you are missing.MO
O
B
MM
E
森L森LE森
L
LDoes a DO SQLite db not lock the entire database too though?
D
A
wrangler d1 insightsSELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY created DESC LIMIT ?3 OFFSET ?4userIdtagLIMITLIMITrows_readEXPLAIN