For Workers & Pages, what is the name of the domain?
cesspit.pages.dev
What is the issue or error you’re encountering
SQL query did not hit index
What steps have you taken to resolve the issue?
CREATE TABLE IF NOT EXISTS “conf” (
“key” TEXT,
“value” TEXT,
PRIMARY KEY(“key”)
);
CREATE TABLE IF NOT EXISTS “message” (
“uid” INTEGER NOT NULL DEFAULT 0,
“type” INTEGER NOT NULL DEFAULT 0,
“pid” INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS “post” (
“pid” INTEGER,
“tid” INTEGER NOT NULL DEFAULT (0),
“uid” INTEGER NOT NULL DEFAULT (0),
“time” INTEGER NOT NULL DEFAULT (0),
“access” INTEGER NOT NULL DEFAULT (0),
“quote_pid” INTEGER NOT NULL DEFAULT (0),
“content” TEXT NOT NULL DEFAULT ,
PRIMARY KEY(“pid”)
);
CREATE TABLE IF NOT EXISTS “thread” (
“tid” INTEGER,
“uid” INTEGER NOT NULL DEFAULT (0),
“time” INTEGER NOT NULL DEFAULT (0),
“access” INTEGER NOT NULL DEFAULT (0),
“is_top” INTEGER NOT NULL DEFAULT (0),
“last_time” INTEGER NOT NULL DEFAULT (0),
“last_uid” INTEGER NOT NULL DEFAULT (0),
“posts” INTEGER NOT NULL DEFAULT (0),
“subject” TEXT NOT NULL DEFAULT ,
CONSTRAINT “THREAD_PK” PRIMARY KEY(“tid”)
);
CREATE TABLE IF NOT EXISTS “user” (
“uid” INTEGER,
“gid” INTEGER NOT NULL DEFAULT (0),
“time” INTEGER NOT NULL DEFAULT (0),
“mail” TEXT NOT NULL DEFAULT UNIQUE COLLATE NOCASE,
“name” TEXT NOT NULL DEFAULT UNIQUE COLLATE NOCASE,
“hash” TEXT NOT NULL DEFAULT ,
“salt” TEXT NOT NULL DEFAULT ,
“threads” INTEGER NOT NULL DEFAULT (0),
“posts” INTEGER NOT NULL DEFAULT (0),
“credits” INTEGER NOT NULL DEFAULT (0),
“golds” INTEGER NOT NULL DEFAULT (0),
CONSTRAINT “USER_PK” PRIMARY KEY(“uid”)
);
CREATE INDEX IF NOT EXISTS “message:uid,type,pid” ON “message” (
“uid”,
“type”,
“pid”
);
CREATE INDEX IF NOT EXISTS “post:access,tid,pid” ON “post” (
“access”,
“tid”,
“pid”
);
CREATE INDEX IF NOT EXISTS “thread:access,is_top,last_time” ON “thread” (
“access”,
“is_top”,
“last_time”
);
CREATE INDEX IF NOT EXISTS “thread:access,uid,time” ON “thread” (
“access”,
“uid”,
“time”
);
What are the steps to reproduce the issue?
We used Drizzle to build a SQL query, which is:
select “post”.“pid”, “post”.“tid”, “post”.“uid”, “post”.“time”, “post”.“access”, “post”.“quote_pid”, “post”.“content”, “user”.“name”, “user”.“credits”, “user”.“gid”, “QuotePost”.“content”, “QuoteUser”.“name”
from “post”
left join “user” on “post”.“uid” = “user”.“uid”
left join “post” “QuotePost” on (“post”.“quote_pid” <> “post”.“tid” and “QuotePost”.“pid” = “post”.“quote_pid” and “QuotePost”.“access” = 0)
left join “user” “QuoteUser” on “QuoteUser”.“uid” = “QuotePost”.“uid”
where (“post”.“access” = 0 and ((“post”.“tid” = 223771 and “post”.“pid” = 223775) or “post”.“tid” = 223771)) order by “post”.“pid” asc limit 10
It costs 87.98k rows read per query, apparently it didn’t hit Indexes.
Run the same query on local SQLite, it correctly hit Indexes.
I can confirm we have same indexes on D1 database.
Seems explainer of D1 is not as smart as SQLite?
