e.g. ``` ➜ wrangler d1 execute db-wnam --command "SELECT * FROM [Order] WHERE ShipRegion LIKE '%Wes

e.g.

➜  wrangler d1 execute db-wnam --command "SELECT * FROM [Order] WHERE ShipRegion LIKE '%Western%' LIMIT 10" --json | jq '.[].meta.rows_read'
18 <-- we had to scan 18 rows to find 10 that matched our filter

➜  wrangler d1 execute db-wnam --command "SELECT * FROM [Order] WHERE ShipRegion LIKE '%Western%' LIMIT 10" --json | jq '.[].meta.rows_read'
18 <-- we had to scan 18 rows to find 10 that matched our filter

➜  wrangler d1 execute db-wnam --command "SELECT * FROM [Order] WHERE ShipRegion LIKE '%Western%' LIMIT 10" --json | jq '.[].meta.rows_read'
18 <-- we had to scan 18 rows to find 10 that matched our filter

➜  wrangler d1 execute db-wnam --command "SELECT * FROM [Order] WHERE ShipRegion LIKE '%Western%' LIMIT 10" --json | jq '.[].meta.rows_read'
18 <-- we had to scan 18 rows to find 10 that matched our filter

Unsmart•8/18/23, 5:44 PM

hmm that seems weird I feel like order shouldnt matter it would just scan and the first 10 matches return and doesnt look at the rest only if you add an order by

UUnsmart hmm that seems weird I feel like order shouldnt matter it would just scan and th...

Matt SilverlockOP•8/18/23, 5:45 PM

There's no ORDERORDER in that query. But it has to scan the table somehow - usually a binary search. It's going to keep searching (scanning) until it has matched all possible records, or hit the LIMITLIMIT

Unsmart•8/18/23, 5:47 PM

Yeah there needs to be some order but I think we are both thinking the same thing. I originally read that as like it will read the entire table even if you already found a number of records matching the limit but it will find only the first rows up to limit but it can possibly scan the entire table if there are not enough to fill up the limit

Unsmart•8/18/23, 5:49 PM

But like if you have 100 rows with 50% being value "1" and 50% being "2" and you do a where value = "1" if you get insanely lucky with the scan it could read as low as 50 rows and very bad luck would read all 100. something like that

UUnsmart Yeah there needs to be some order but I think we are both thinking the same thin...

Matt SilverlockOP•8/18/23, 5:49 PM

yep, exactly. It could scan the whole table and only find 5 results for LIMIT 10LIMIT 10

UUnsmart But like if you have 100 rows with 50% being value "1" and 50% being "2" and you...

Matt SilverlockOP•8/18/23, 5:51 PM

Yep! Just Binary Search Things

UUnsmart hmm that seems weird I feel like order shouldnt matter it would just scan and th...

Matt SilverlockOP•8/18/23, 5:54 PM

To add: you can have a sorted index (in D1 it doesn't matter which direction; the query engine can just scan the other way) which will further improve index efficiency if you are doing a lot of ORDER BY <some_date_column>ORDER BY <some_date_column> queries. I probably need to add this to the docs.

MMatt Silverlock To add: you _can_ have a sorted index (in D1 it doesn't matter which direction; ...

Matt SilverlockOP•8/18/23, 5:55 PM

some reading if you're interested: https://stackoverflow.com/a/57082926/556573

Stack Overflow

Sort order for sqlite index

There is sort order when creating index for sqlite.

https://sqlite.org/lang_createindex.html
Each column name or expression can be followed by one of the "ASC" or "DESC" keywords to indicate ...

Unsmart•8/18/23, 5:57 PM

yeah thanks

MMatt Silverlock e.g. ``` ➜ wrangler d1 execute db-wnam --command "SELECT * FROM [Order] WHERE S...

Hello, I’m Allie!•8/18/23, 6:39 PM

Thanks for clarifying!

chientrm.com•8/18/23, 8:12 PM

do we need more than SQL LIKESQL LIKE?

MMatt Silverlock D1 now returns `rows_written` and `rows_read` in the metadata for every query. D...

Matt SilverlockOP•8/18/23, 9:53 PM

https://developers.cloudflare.com/d1/changelog/#row-count-now-returned-per-query

Changelog · Cloudflare D1 docs

D1 now returns a count of rows_written and rows_read for every query executed, allowing you to assess the cost of query for both pricing and index …

PPaganMuffin If I understand it correctly. e.g. ```SELECT * FROM table WHERE index_column =...

Matt SilverlockOP•8/18/23, 10:54 PM

I don’t know where you get 51 from? Your second example doesn’t seem right either. The actual behavior depends on if the index is ordered as well.

MMatt Silverlock D1 now returns `rows_written` and `rows_read` in the metadata for every query. D...

TigersWay•8/18/23, 11:02 PM

I just had a quick check to read those new rows_writtenrows_written and rows_readrows_read and I am kinda surprised to find written rows in a SELECT query

TigersWay•8/18/23, 11:03 PM

And way more read than there is rows!

TTigersWay I just had a quick check to read those new `rows_written` and `rows_read` and I ...

Matt SilverlockOP•8/18/23, 11:23 PM

What is your query? A SELECT isn’t guaranteed to be not write rows.

Unsmart•8/18/23, 11:28 PM

sql is confusing

CCole Xemi Is this going to be visible in the dashboard eventually?

Matt SilverlockOP•8/19/23, 12:26 AM

Yes. Within the next week or two

Hello, I’m Allie!•8/19/23, 7:32 AM

D1 Pricing

Pricing · Cloudflare D1 docs

While in public Alpha, D1 is currently free to use on all Workers plans. Refer to the our recent announcement ) for more information.

jahir9991•8/19/23, 7:59 AM

In production pages my get is working but writhing on db is not working is says no authorization can anyone aware of that i am using Sveltekit and drizzle and all binding are okay as i can get the data , my local dev is also working fine

kian•8/19/23, 8:06 AM

Do you have an example of a query that is failing?

jahir9991•8/19/23, 8:26 AM

Put and post is failing

jahir9991•8/19/23, 8:26 AM

It says not authorized

Kkian Do you have an example of a query that is failing?

jahir9991•8/19/23, 8:28 AM

Do i need to set any env variable rather than d1 binding

Kkian Do you have an example of a query that is failing?

jahir9991•8/19/23, 8:30 AM

https://staging.jahir.pages.dev/api/skills?q= this is get request which is working fine and serving data from d1

TigersWay•8/19/23, 8:36 AM

Following the apparition of rows_writtenrows_written and rows_readrows_read in every result, could we imagine some total_rows_writtentotal_rows_written and total_rows_readtotal_rows_read in a "status" request?

Original message was deleted

Matt SilverlockOP•8/19/23, 8:38 AM

Keep in mind D1 won’t charge extra for replication (and it won’t multiply storage) or Time Travel (point in time recovery). $5/mo gets you 50M writes and 25B reads, 30 days Time Travel and storage.

TTigersWay Following the apparition of `rows_written` and `rows_read` in every result, coul...

Matt SilverlockOP•8/19/23, 8:39 AM

Can you expand? All of this will be in the GraphQL analytics API & dashboard

TTigersWay Following the apparition of `rows_written` and `rows_read` in every result, coul...

Matt SilverlockOP•8/19/23, 8:39 AM

e.g. https://developers.cloudflare.com/d1/platform/metrics-analytics/

Metrics and analytics · Cloudflare D1 docs

D1 exposes database analytics that allow you to inspect query volume, response size, and query latency across all and/or each database in your …

TigersWay•8/19/23, 8:40 AM

I guess I'll have to jump in that one! Thanks

Kkian Do you have an example of a query that is failing?

jahir9991•8/19/23, 11:20 AM

a hello @kiannh thanks for your replay , i got the issue , i added transaction in my query !!!!!!!!!!!!!, and its become rude ,
any way to use transaction ?

chientrm.com•8/19/23, 12:34 PM

Consider D1 is so superior, what's holding cloudflare

back from pushing the limits over 500MB?

Jjahir9991 a hello @kiannh thanks for your replay , i got the issue , i added ***transact...

Cyb3r-Jak3•8/19/23, 1:37 PM

Not currently. It is planned but no ETA. If you use .batch().batch() it gets auto transaction

chientrm.com•8/19/23, 4:40 PM

I'm using EXPLAINEXPLAIN statement. The column name is messed up?

Screen_Shot_2023-08-19_at_11.39.44_PM.png

Cchientrm.com Consider D1 is so superior, what's holding <:cloudflare:1026272852900581496> bac...

DebianArch•8/19/23, 7:22 PM

Cloudflare-d1 is very promising, the only thing I'm concerned about as-well is the 500Mb limit

DebianArch•8/19/23, 7:23 PM

Cloudflare D1 is pretty beastly when paired with drizzle orm makes relational queries so easy

DDebianArch Cloudflare D1 is pretty beastly when paired with drizzle orm makes relational qu...

chientrm.com•8/20/23, 2:00 AM

Cchientrm.com I'm using `EXPLAIN` statement. The column name is messed up? 🤔

Matt SilverlockOP•8/20/23, 11:27 AM

That’s correct.

When the EXPLAIN keyword appears by itself it causes the statement to behave as a query that returns the sequence of virtual machine instructions it would have used to execute the command had the EXPLAIN keyword not been present. When the EXPLAIN QUERY PLAN phrase appears, the statement returns high-level information regarding the query plan that would have been used.

https://www.sqlite.org/lang_explain.html

MMatt Silverlock That’s correct. > When the EXPLAIN keyword appears by itself it causes the state...

chientrm.com•8/20/23, 11:30 AM

totally not understand

JJorgeDev Will this be increased in the future? Does anyone know?

Hello, I’m Allie!•8/20/23, 12:40 PM

Many of these limits will increase during D1’s public alpha. Join the #d1-database channel in the Cloudflare Developer Discord to keep up to date with changes.
From the Docs

Limits · Cloudflare D1 docs

Many of these limits will increase during D1’s public alpha. Join the #d1-open-alpha channel in the Cloudflare Developer Discord to keep up to date …

Cyb3r-Jak3•8/20/23, 12:53 PM

They have also said 1 GB for DB sizes in blog posts

chientrm.com•8/20/23, 12:54 PM

also notice that new backend seem to use less space than legacy.

chientrm.com•8/20/23, 2:31 PM

also D1 make me love SQL again

jahir9991•8/20/23, 6:50 PM

@Matt is there any way i can access d1 local or remote in sveltekit without build version

jahir9991•8/20/23, 6:53 PM

My bad !!!!

Jjahir9991 @Matt is there any way i can access d1 local or remote in sveltekit without bui...

chientrm.com•8/20/23, 7:17 PM

https://www.npmjs.com/package/cf-workers-proxy

npm

cf-workers-proxy

Enable Cloudflare Workers runtime for local development. Latest version: 0.4.0, last published: 10 days ago. Start using cf-workers-proxy in your project by running npm i cf-workers-proxynpm i cf-workers-proxy. There are no other projects in the npm registry using cf-workers-proxy.

e.g. ``` ➜ wrangler d1 execute db-wnam --command "SELECT * FROM [Order] WHERE ShipRegion LIKE '%Wes

Similar Threads

Similar Threads

Similar Threads