M
DDM
insert licenses (id) values (1); while the dash ui is trying to run this:MD
ISELECT * from test WHERE id=100 returns "rows_read": 1 from 150-row table at this moment.I
KEXPLAIN QUERY PLAN, you'll see that your index on id results in SEARCH being used which is only scanning a subset of tables, whereas other keys will show SCAN which is a full table scan.K
KI
QKQ
UU
A prepared SQL statement must contain only one statementK; at the endUK' ' or '\n' then it'll reject it as it thinks there's more statements to come.KUUKKwrangler d1 execute ... --command?UKUwrangler d1 execute my-db --local --file=path/to/file.sqlKKK
LK
U
MMKL
C
CREATE TABLE IF NOT EXISTS Messages (id TEXT, userId TEXT, content TEXT); but when I try to select from Messages it says the table doesn't exist?ML
C
--local
Jposts in your case) and do the LIMIT there, and then do the joins in the outer table, if that makes sense.
JL
SSIFor example, if the users table has an index on a timestamp columnFor https://developers.cloudflare.com/d1/platform/pricing/, I still cannot understand how rows read works at this moment. Rows reading measurement only shows example of full table scan, but not others such like partial read (filterable, join etc).created_at, the querySELECT * FROM users WHERE created_at > ?1would only need to read a subset of the table.
I
insert licenses (id) values (1);{"sql":"INSERT INTO [licenses] (\"id\", \"status\", \"plan\", \"created_at\", \"expires_at\") VALUES ($0,$1,$2,$3,$4)","params":["2",null,null,null,null]}SELECT * from test WHERE id=100"rows_read": 1EXPLAIN QUERY PLANidSEARCHSCANA prepared SQL statement must contain only one statement;;' ''\n'wrangler d1 execute ... --commandwrangler d1 execute my-db --local --file=path/to/file.sql SELECT
posts.id,
posts.title,
posts.updatedOn,
substr(posts.body, 0, 20) as body,
users.firstName || ' ' || users.lastName as author,
count(comments.id) as commentCount,
categories.title as category,
COUNT() OVER() as total
FROM posts
left outer join users
on posts.userid = users.id
left outer join comments
on comments.postId = posts.id
left outer join categoriesToPosts
on categoriesToPosts.postId = posts.id
left outer join categories
on categoriesToPosts.categoryId = categories.id
group by posts.id
order by posts.updatedOn desc
limit 10
offset 0CREATE TABLE IF NOT EXISTS Messages (id TEXT, userId TEXT, content TEXT);--localpostscreated_atSELECT * FROM users WHERE created_at > ?1CREATE TABLE IF NOT EXISTS licenses (
id integer PRIMARY KEY AUTOINCREMENT,
status text NOT NULL DEFAULT 'active',
plan text NOT NULL DEFAULT 'basic',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
expires_at DATETIME DEFAULT CURRENT_TIMESTAMP
);INSERT INTO verification_keys (id, content, account_name) VALUES ('6de221d9-4546-4599-ae7a-956632c9f6c9', 'c9948fff-9da3-4713-a169-9c434dbb1681', 'user1');