Indexes don’t store row counts. It’s not possible to do a `count` and get one row read back in SQLit

MMarcel Overdijk I assume 174 + 1 as it accesses the index? But anyway, I cannot seem to get coun...

M

Matt SilverlockOP•5/15/24, 9:46 PM

https://marc.info/?l=sqlite-users&m=112679232205850

J

johtso•5/15/24, 11:43 PM

I'm getting a "D1_ERROR: no such table" error when running my worker locally. I've applied my migrations and have checked the table exists using "PRAGMA table_list;". The database id is definitely the same as the one that wrangler says it's binding to. Is there anything else I can check?

J

johtso•5/15/24, 11:49 PM

when I try to list the tables from my worker there seem to be none at all (just _cf_KV, sqlite_schema and sqlite_temp_schema)

J

johtso•5/15/24, 11:52 PM

oh wait.. I have a monorepo and the database schema is in a different package

J

johtso•5/15/24, 11:52 PM

that means when I ran my migrations locally, they were applied to the d1 database stored in that package's directory

J

johtso•5/15/24, 11:52 PM

but the one in my workers directory is empty

J

johtso•5/15/24, 11:53 PM

not sure what I'm supposed to be doing here

J

johtso•5/15/24, 11:58 PM

and now I bump into this issue https://github.com/cloudflare/workers-sdk/issues/4713

GitHub

🐛 BUG: D1 migrations/executions with multiple statements & quote ma...

Which Cloudflare product(s) does this pertain to? D1 What version(s) of the tool(s) are you using? 3.22.3 [Wrangler] What version of Node are you using? 20.9.0 What operating system are you using? ...

J

johtso•5/16/24, 12:22 AM

ok, so after deleting the multi-line comment from the migration I've been able to apply it to my remote database

J

johtso•5/16/24, 12:22 AM

now I can run wrangler pointing to that database and everything's working

MMatt Silverlock Indexes don’t store row counts. It’s not possible to do a `count` and get one ro...

I

Isaac McFadyen•5/16/24, 3:00 AM

https://canary.discord.com/channels/595317990191398933/992060581832032316/1237957306336149635
Interesting, that's the opposite of when Glen said here.

IIsaac McFadyen https://canary.discord.com/channels/595317990191398933/992060581832032316/123795...

M

Matt SilverlockOP•5/16/24, 3:19 AM

I’m linking to comments (old, but still correct) from the core maintainer and you can find many SO answers confirming the same. I can’t speak to why Glen said otherwise (aka “show me the code”)

MMatt Silverlock I’m linking to comments (old, but still correct) from the core maintainer and yo...

I

Isaac McFadyen•5/16/24, 3:40 AM

I just spun up a test DB and I seem to see the opposite per this Stack Overflow: https://stackoverflow.com/questions/18807304/is-count-constant-time-in-sqlite-and-if-not-what-are-alternatives (edit: had the wrong link)

I

Isaac McFadyen•5/16/24, 3:41 AM

The some_string is not indexed, but there is an index on another column in the table.

I

Isaac McFadyen•5/16/24, 3:41 AM

The some_string COUNT takes 4s whereas the * count (which uses the index as you can see from the query planner there,

USING COVERING INDEX test_index

USING COVERING INDEX test_index

USING COVERING INDEX test_index

USING COVERING INDEX test_index) takes 0.3s

I

Isaac McFadyen•5/16/24, 3:41 AM

This is in a 3.13GB DB

IIsaac McFadyen The some_string COUNT takes 4s whereas the * count (which uses the index as you ...

M

Matt SilverlockOP•5/16/24, 3:44 AM

Does it scan every row or not, though? That’s what matters here.

MMatt Silverlock Does it scan every _row_ or not, though? That’s what matters here.

I

Isaac McFadyen•5/16/24, 3:45 AM

I'm assuming not given that the index is queried in 11x less time than the scan of the table?

I

Isaac McFadyen•5/16/24, 3:46 AM

Not sure why else it would take that much less time but maybe there's some reason that it would and still read every row somehow.

IIsaac McFadyen I'm assuming not given that the index is queried in 11x less time than the scan ...

M

Matt SilverlockOP•5/16/24, 3:46 AM

Scanning fewer columns than

. I’m not at a computer but try it with D1 (or libsql) so you can see the row counts. Time isn’t accurate.

M

Matt SilverlockOP•5/16/24, 3:49 AM

(I would LOVE to wrong here but when we tested this and looked at the code a few months ago - the SQLite mailing list stands true)

C

Christer•5/16/24, 7:26 AM

Hi there! Is there a way to rerun migrations? I'm still doing a lot of development and want to drop a re-create my db.

J

jkomyno•5/16/24, 11:46 AM

Hi, I think I have encountered a behavioral difference between SQLite and D1. I'm only using supported commands and PRAGMAs. Here goes:
- I start with the following schema

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

- I then apply the following migration

PRAGMA defer_foreign_keys=ON;
PRAGMA foreign_keys=OFF;
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
PRAGMA foreign_keys=ON;
PRAGMA defer_foreign_keys=OFF;

PRAGMA defer_foreign_keys=ON;
PRAGMA foreign_keys=OFF;
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
PRAGMA foreign_keys=ON;
PRAGMA defer_foreign_keys=OFF;

PRAGMA defer_foreign_keys=ON;
PRAGMA foreign_keys=OFF;
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
PRAGMA foreign_keys=ON;
PRAGMA defer_foreign_keys=OFF;

PRAGMA defer_foreign_keys=ON;
PRAGMA foreign_keys=OFF;
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
PRAGMA foreign_keys=ON;
PRAGMA defer_foreign_keys=OFF;

This migration works on standard SQLite, but fails with

foreign key mismatch - "new_Post" referencing "User"

foreign key mismatch - "new_Post" referencing "User"

foreign key mismatch - "new_Post" referencing "User"

foreign key mismatch - "new_Post" referencing "User" on D1. Any help, please?

Jjkomyno Hi, I think I have encountered a behavioral difference between SQLite and D1. I'...

H

harshil1712•5/16/24, 12:44 PM

Hey, it seems like the issue is with the way you have ordered the statements. You are referencing the

USER

USER

USER

USER table, even before it gets modified. Something like the below should work:

PRAGMA defer_foreign_keys = on;
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
PRAGMA defer_foreign_keys = off;

PRAGMA defer_foreign_keys = on;
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
PRAGMA defer_foreign_keys = off;

PRAGMA defer_foreign_keys = on;
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
PRAGMA defer_foreign_keys = off;

PRAGMA defer_foreign_keys = on;
CREATE TABLE "new_User" (
    "uid" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);
DROP TABLE "User";
ALTER TABLE "new_User" RENAME TO "User";
CREATE TABLE "new_Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "authorId" INTEGER,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("uid") ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO "new_Post" ("authorId", "id") SELECT "authorId", "id" FROM "Post";
DROP TABLE "Post";
ALTER TABLE "new_Post" RENAME TO "Post";
PRAGMA defer_foreign_keys = off;

Hharshil1712 Hey, it seems like the issue is with the way you have ordered the statements. Yo...

J

jkomyno•5/16/24, 1:06 PM

Hi, thanks for the answer. You're right, what you provided works. My question is: why does my snippet only fail with D1, and not with SQLite? It seems to be that there are subtle differences in the way foreign keys are enforced / and or their checks are deferred within migrations?

Jjkomyno Hi, thanks for the answer. You're right, what you provided works. My question is...

H

harshil1712•5/16/24, 2:05 PM

That's a question, I don't have an answer for right now. I have joined recently, and still learning!

Hharshil1712 That's a question, I don't have an answer for right now. I have joined recently,...

J

jkomyno•5/16/24, 2:46 PM

Thanks for the transparency and for the time, regardless. Perhaps Matt may know more about this?

Jjkomyno Thanks for the transparency and for the time, regardless. Perhaps Matt may know ...

M

Matt SilverlockOP•5/16/24, 3:32 PM

D1 doesn’t allow you to turn off foreign keys, so

PRAGMA foreign_keys=off

PRAGMA foreign_keys=off

PRAGMA foreign_keys=off

PRAGMA foreign_keys=off is a no-op (per SQLite spec). The

defer

defer

defer

defer works, as you can see.

AAnay-208 does cloudflare has any other database like d1, which supports higher storage? A...

Z

zwily•5/16/24, 5:16 PM

No, but they partner with people like Neon for Saas dbs

Z

zwily•5/16/24, 5:16 PM

Is drizzle still people's favorite for typed querying with D1 today?

AAnay-208 does cloudflare has any other database like d1, which supports higher storage? A...

M

Max (@rozenmd)•5/16/24, 6:23 PM

Hyperdrive + BYO Postgres DB

F

Fabian•5/16/24, 8:01 PM

When would it be the case that rows_written != changes?
I have a simple insert query and Miniflare (imo) incorrectly reports 2 rows written here

FFabian When would it be the case that rows_written != changes? I have a simple insert q...

M

Max (@rozenmd)•5/16/24, 8:13 PM

an index counts as a row written

F

Fabian•5/16/24, 8:23 PM

ah ok, that makes sense. Probably overread that somewhere in the docs. So what I would probably be looking for if I want the "real" amount of created/updated rows is changes, right?

J

jepp•5/16/24, 8:43 PM

Hi is there a way to upload a sqlite DB via API?

J

jepp•5/16/24, 8:45 PM

Ok. Found out how it works. Nevermind

R

roftt•5/17/24, 6:28 AM

Is it expected that this batch operation makes 100 write queries, not 1 (displayed in Dashboard's DB Metrics)?

var batch_arr = [];  
const delete_r = env.DB.prepare("DELETE FROM table");  
batch_arr.push(delete_r);        
const stmt = env.DB.prepare("INSERT INTO table (n, v) VALUES (?1, ?2)");  
  
for(var i = 0; i < 100; i++) {        
    batch_arr.push(stmt.bind( i, 'str' ));
}
const batch = await env.DB.batch(batch_arr);

var batch_arr = [];  
const delete_r = env.DB.prepare("DELETE FROM table");  
batch_arr.push(delete_r);        
const stmt = env.DB.prepare("INSERT INTO table (n, v) VALUES (?1, ?2)");  
  
for(var i = 0; i < 100; i++) {        
    batch_arr.push(stmt.bind( i, 'str' ));
}
const batch = await env.DB.batch(batch_arr);

var batch_arr = [];  
const delete_r = env.DB.prepare("DELETE FROM table");  
batch_arr.push(delete_r);        
const stmt = env.DB.prepare("INSERT INTO table (n, v) VALUES (?1, ?2)");  
  
for(var i = 0; i < 100; i++) {        
    batch_arr.push(stmt.bind( i, 'str' ));
}
const batch = await env.DB.batch(batch_arr);

var batch_arr = [];  
const delete_r = env.DB.prepare("DELETE FROM table");  
batch_arr.push(delete_r);        
const stmt = env.DB.prepare("INSERT INTO table (n, v) VALUES (?1, ?2)");  
  
for(var i = 0; i < 100; i++) {        
    batch_arr.push(stmt.bind( i, 'str' ));
}
const batch = await env.DB.batch(batch_arr);

MMatt Silverlock Indexes don’t store row counts. It’s not possible to do a `count` and get one ro...

M

Marcel Overdijk•5/17/24, 8:36 AM

then the ealier comment on this channel was incorrect I assume?

Rroftt Is it expected that this batch operation makes 100 write queries, not 1 (display...

I

Isaac McFadyen•5/17/24, 1:47 PM

It's not billed on queries but rows. You are inserting 100 rows (writes) so you'll be billed for 100 writes regardless of how many queries you split it into.

I

Isaac McFadyen•5/17/24, 1:50 PM

Ah, think I misunderstood. Are you referring to D1 analytics or billing?

IIsaac McFadyen It's not billed on queries but rows. You are inserting 100 rows (writes) so you'...

R

roftt•5/17/24, 1:50 PM

Yes, I'm aware of pricing by rows, thank you. I was just checking if it's a bug since I thought the batch operation should be executed in one query.

I

Isaac McFadyen•5/17/24, 1:51 PM

As far as I know a D1 batch is just a bunch of statements that are wrapped in a transaction for you by the D1 engine internally.

I

Isaac McFadyen•5/17/24, 1:51 PM

So it'll still be more than 1 query if you have more than 1 item in the batch.

M

Max (@rozenmd)•5/17/24, 2:41 PM

batch just saves you network trips from the Worker to D1

K

kindaraffy•5/19/24, 2:30 PM

Is anyone else experience degraded D1 performance when querying data? I have a 640kB database and it will sometimes (around 1/8 times) take a really long time (~ 5-10 secs) to execute the query.

I was blaming my SQL queries at the start, but I just saw this happen on a

SELECT * FROM <table>

SELECT * FROM <table>

SELECT * FROM <table>

SELECT * FROM <table> query against a table with 4 rows from the D1 dashboard console.

I've noticed this occurring from the start of this day. I've attached a screenshot of my network health from

speed.cloudflare.com

speed.cloudflare.com

speed.cloudflare.com

speed.cloudflare.com. I'm in Melb, Aus.

K

kindaraffy•5/19/24, 2:43 PM

K

kindaraffy•5/19/24, 2:44 PM

D1 returns a result at around

0:28

0:28

0:28

0:28

Kkindaraffy Is anyone else experience degraded D1 performance when querying data? I have a 6...

M

Max (@rozenmd)•5/19/24, 7:05 PM

whats your databaseId?

Indexes don’t store row counts. It’s not possible to do a `count` and get one row read back in SQLit

Similar Threads

Similar Threads

Similar Threads