I have a query: `SELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY created DESC LIMIT ?3

I have a query:

SELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY created DESC LIMIT ?3 OFFSET ?4

SELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY created DESC LIMIT ?3 OFFSET ?4

SELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY created DESC LIMIT ?3 OFFSET ?4

SELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY created DESC LIMIT ?3 OFFSET ?4
This query works perfectly for me but it is inefficient even if I have indexes on

userId

userId

userId

userId and

tag

tag

tag

tag

For example if my

LIMIT

LIMIT

LIMIT

LIMIT was 100 I would expect 100

rows_read

rows_read

rows_read

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?

DDean so i accidently deleted 2 D1 databases without removing the worker binding throu...

Max (@rozenmd)•1/18/25, 4:25 PM

wait you can delete dbs while they're still bound? sounds like a bug

MMax (@rozenmd)wait you can delete dbs while they're still bound? sounds like a bug

DaniFoldi•1/18/25, 4:33 PM

yeah some bindings prevent it, others don't, it's not just D1

Oo7 I have a query: `SELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY c...

lambrospetrou•1/18/25, 5:05 PM

For 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

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.

In order to optimize your queries, run it once with the prefix EXPLAINEXPLAIN 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.

Max (@rozenmd)•1/18/25, 5:19 PM

LIMIT means limit the number of rows returned to X, not limit the number of rows scanned

Llambrospetrou > For example if my LIMIT was 100 I would expect 100 rows_read without the order...

o7OP•1/18/25, 8:51 PM

yes, upon further testing (OFFSET not at 0), it shows more rows_read than the LIMIT.

But the explain query plan shows I'm using the correct index and its just the ORDER that's reading every row with the tag (twice)

o7OP•1/18/25, 8:54 PM

if no optimizations can be made with an index then its fine since it gives me an excuse to rewrite this part with a search function :p

besimking•1/18/25, 9:53 PM

Hey guys!
Is there any way to directly upload an existing sql to d1? I have an sql db I want to upload it directly

Moh•1/18/25, 11:05 PM

What db is it

Moh•1/18/25, 11:06 PM

There's differences between SQLite and other DBs if the original isn't SQLite you might need to do some changes first

Bbesimking Hey guys! Is there any way to directly upload an existing sql to d1? I have an s...

lambrospetrou•1/18/25, 11:27 PM

https://developers.cloudflare.com/d1/best-practices/import-export-data/

Elliot Hesp•1/19/25, 4:35 PM

Any change d1 will ever support transactions?

森

森@VerseLink (RW)•1/19/25, 4:51 PM

isn't batch a kind of transaction? though if you need to do works in between sql execution then DO might be the choice

EElliot Hesp Any change d1 will ever support transactions?

lambrospetrou•1/19/25, 4:51 PM

The reason we don't support transactions right now is that because D1 is a remote database, if you were doing multiple round trips in your worker code to the database within a transaction, your throughput would probably be extremely low, since you would be blocking the entire database waiting for 1 worker.

Each query right now is a transaction, e.g. all queries within a batch all run within a transaction.

森

森@VerseLink (RW)•1/19/25, 4:52 PM

yeh that's what i imagined too, though there seems not to have news with workers running on the same machine with D1 to run transaction (I remembered I was hype when that was "hopefully planned"), but I think DO replaces that kind of well (except we get charged by execution time lol)

森森@VerseLink (RW)yeh that's what i imagined too, though there seems not to have news with workers...

lambrospetrou•1/19/25, 4:53 PM

Running on the same machine as sqlite, IS literally the SQLite DOs. If that's what your usecase needs, to intermix logic with lots of back and forth to the database, then SQLite DOs are more suitable than D1, right now at least.

Llambrospetrou The reason we don't support transactions right now is that because D1 is a remot...

Elliot Hesp•1/19/25, 4:55 PM

Interesting thanks! Yeah batches sort of solve some usecases but interactive transactions are the usecase (e.g. Get a user's balance, check it, update it etc)

Does a DO Sqlite db not lock the entire database too though?

Llambrospetrou Running on the same machine as sqlite, IS literally the SQLite DOs. If that's wh...

森

森@VerseLink (RW)•1/19/25, 4:56 PM

yeh that's what i figured too, the more i think about DO SQLite, the more potential I think it can acheive

EElliot Hesp Interesting thanks! Yeah batches sort of solve some usecases but interactive tra...

lambrospetrou•1/19/25, 4:58 PM

DOs only execute a request at a time. Each time you do async IO, then another request is scheduled until the IO is ready.

EElliot Hesp Interesting thanks! Yeah batches sort of solve some usecases but interactive tra...

Larry•1/19/25, 6:10 PM

Does a DO SQLite db not lock the entire database too though?

We've all been taught to believe that dbs should be highly parallel and that "lock"ing is bad, but keep in mind a few things:

A DO's SQLite is not just running in the same datacenter, not just running on the same machine, but running in the same thread as your JavaScript code. There are not even task switching costs. For cache hits, it has essentially no performance difference from accessing instance member data. For cache misses, there is a disk round-trip to a fast local SSD so not much worse.
This is a fundamentally different paradigm. It emphasizes: 1) transactional consistency with requests and compute intermixed. Think of your DO JavaScript as stored procedures; 2) DX by making it much easier to reason about and avoid race conditions.
You scale DO-based architectures by having more DOs, and thus more DBs. This is conceptually similar to sharding in a database except that you have to take care of routing to the correct shard yourself.
Parallizing reads with an eventual but read-your-own writes model is coming soon for D1. I also have heard that it'll come for DOs either at the same time or soon after since D1 is built on DOs.

Dean•1/20/25, 6:24 AM

is dynamic bindings coming with these upcoming changes or do we expect that to come much later?

AngusMa•1/20/25, 9:34 AM

How can I query the table list without system tables like sqlite_sequence

AAngusMa How can I query the table list without system tables like sqlite_sequence

lambrospetrou•1/20/25, 9:37 AM

select * from sqlite_masterselect * from sqlite_master ?

Llambrospetrou `select * from sqlite_master` ?

AngusMa•1/20/25, 9:40 AM

_cf_KV_cf_KV and sqlite_sequencesqlite_sequence are also listed

AngusMa•1/20/25, 9:49 AM

So I can only filter it by myself
Thanks

esselungo•1/20/25, 10:18 AM

I have a few million rows read every hour how can i investigate where this is coming from cause I don't reckon my code is doing that

Eesselungo I have a few million rows read every hour how can i investigate where this is co...

Max (@rozenmd)•1/20/25, 10:33 AM

npx wrangler d1 insights dbname

esselungo•1/20/25, 10:34 AM

thanks!

esselungo•1/20/25, 10:36 AM

is there a plan that allows for more rows read?

esselungo•1/20/25, 10:37 AM

i'm legit thinking of leaving d1 for an external service cause it's crazy I have a very small tool and already am at 17B rows read cause some queries read hundreds of thousands of rows

Eesselungo is there a plan that allows for more rows read?

Max (@rozenmd)•1/20/25, 10:40 AM

the Workers Paid plan gives you 25 billion rows read per month for $5 USD/mo

esselungo•1/20/25, 10:40 AM

yeah i have this one

Eesselungo i'm legit thinking of leaving d1 for an external service cause it's crazy I have...

Max (@rozenmd)•1/20/25, 10:41 AM

Hyperdrive + postgres works really well - but are you using indexes on your queries? it isn't normal to read hundreds of thousands of rows per query

MMax (@rozenmd)Hyperdrive + postgres works really well - but are you using indexes on your quer...

esselungo•1/20/25, 10:41 AM

{
    "query": "delete from \"magnetograms\" where (\"magnetograms\".\"timestamp\" < ? or \"magnetograms\".\"timestamp\" > ?)",
    "avgRowsRead": 119275,
    "totalRowsRead": 715650,
    "avgRowsWritten": 119273,
    "totalRowsWritten": 715638,
    "avgDurationMs": 418.5989,
    "totalDurationMs": 2511.5934,
    "numberOfTimesRun": 6,
    "queryEfficiency": 0
  }

{
    "query": "delete from \"magnetograms\" where (\"magnetograms\".\"timestamp\" < ? or \"magnetograms\".\"timestamp\" > ?)",
    "avgRowsRead": 119275,
    "totalRowsRead": 715650,
    "avgRowsWritten": 119273,
    "totalRowsWritten": 715638,
    "avgDurationMs": 418.5989,
    "totalDurationMs": 2511.5934,
    "numberOfTimesRun": 6,
    "queryEfficiency": 0
  }

like on delete it reads almost a million

esselungo•1/20/25, 10:42 AM

what do you mean by using indeces exactly?

esselungo•1/20/25, 10:42 AM

not sure to see what you mean by that

Max (@rozenmd)•1/20/25, 10:42 AM

https://developers.cloudflare.com/d1/best-practices/use-indexes/

Cloudflare Docs

Use indexes · Cloudflare D1 docs

Indexes enable D1 to improve query performance over the indexed columns for common (popular) queries by reducing the amount of data (number of rows) the database has to scan when running a query.

esselungo•1/20/25, 10:43 AM

thanks i'm going to give it a read

Eesselungo ``` { "query": "delete from \"magnetograms\" where (\"magnetograms\".\"times...

Max (@rozenmd)•1/20/25, 10:45 AM

a super naive index like:
CREATE INDEX idx_magnetograms_timestamp ON magnetograms(timestamp)CREATE INDEX idx_magnetograms_timestamp ON magnetograms(timestamp)
would massively help that delete query (the trade-off for indexes is storage space - the DB needs to store that data somewhere to make it faster)

MMax (@rozenmd)a super naive index like: `CREATE INDEX idx_magnetograms_timestamp ON magnetogra...

esselungo•1/20/25, 10:47 AM

hmm i see what you mean, i would put that on columns used by my biggest queries correct?

Eesselungo hmm i see what you mean, i would put that on columns used by my biggest queries ...

Max (@rozenmd)•1/20/25, 10:50 AM

Yeah - a pretty common index I tend to add is for foreign keys

so like, say you have a table of sensorssensors and another table of datadata, where data stores sensor_idsensor_id, an index on that column speeds up queries like SELECT * FROM data WHERE sensor_id = ?SELECT * FROM data WHERE sensor_id = ?

if you also store a timestamp in that table you might want to index against that too so filtering by date is fast, so it'd be like

CREATE INDEX idx_data_sensor_id_timestamp_desc ON data(sensor_id, timestamp DESC)CREATE INDEX idx_data_sensor_id_timestamp_desc ON data(sensor_id, timestamp DESC)

MMax (@rozenmd)Yeah - a pretty common index I tend to add is for foreign keys so like, say yo...

esselungo•1/20/25, 10:52 AM

I see, thanks a lot for the insight
it will also decrease the amount of rows read for queries I assume?

Eesselungo I see, thanks a lot for the insight it will also decrease the amount of rows rea...

Max (@rozenmd)•1/20/25, 10:52 AM

massively, yes

Max (@rozenmd)•1/20/25, 10:53 AM

also between 10x and 100x faster queries depending on how much data you have

Max (@rozenmd)•1/20/25, 10:54 AM

(also this isn't just a D1 thing, i've seen queries go from 100-150ms to 1-5ms after adding indexes in Postgres)

DDeepak Singh is this ok to connect to d1 databse and execute query with directly POST API in ...

Max (@rozenmd)•1/20/25, 11:33 AM

it'll work, just slow and subject to the Cloudflare API rate limit (1200 reqs/5min)

MMax (@rozenmd)it'll work, just slow and subject to the Cloudflare API rate limit (1200 reqs/5m...

lambrospetrou•1/20/25, 11:37 AM

It's 10x slower than with using a worker (for now), so for internal tools it's fine, but actual use-cases in production where you expected latencies around 50-200ms, it won't happen. So, do it with the right expectations.

Max (@rozenmd)•1/20/25, 11:39 AM

in short, if performance matters to you, put a Worker up and call it from Java: https://github.com/elithrar/http-api-d1-example

lambrospetrou•1/21/25, 6:43 PM

Anyone spotted these improvements in their metrics for Workers using D1?

https://developers.cloudflare.com/d1/platform/changelog/#2025-01-07

Chaika•1/21/25, 6:47 PM

yes :MeowHeartCloudflare:

I have a query: `SELECT * FROM objects WHERE userId = ?1 AND tag = ?2 ORDER BY created DESC LIMIT ?3

Similar Threads

Similar Threads

Similar Threads