btw I also tried a new index: ```sql CREATE INDEX project_tiktok_link_id_idx ON project (tiktok_link

btw I also tried a new index:

CREATE INDEX project_tiktok_link_id_idx ON project (tiktok_link, id);

CREATE INDEX project_tiktok_link_id_idx ON project (tiktok_link, id);

CREATE INDEX project_tiktok_link_id_idx ON project (tiktok_link, id);

CREATE INDEX project_tiktok_link_id_idx ON project (tiktok_link, id);

and do the query

SELECT ... FROM project INDEXED BY project_tiktok_link_id_idx WHERE tiktok_link IS NULL and id < '01HQ81GRDX539N2EJSW1ECGRMY'

SELECT ... FROM project INDEXED BY project_tiktok_link_id_idx WHERE tiktok_link IS NULL and id < '01HQ81GRDX539N2EJSW1ECGRMY'

SELECT ... FROM project INDEXED BY project_tiktok_link_id_idx WHERE tiktok_link IS NULL and id < '01HQ81GRDX539N2EJSW1ECGRMY'

SELECT ... FROM project INDEXED BY project_tiktok_link_id_idx WHERE tiktok_link IS NULL and id < '01HQ81GRDX539N2EJSW1ECGRMY'

and it seems to use the index now

Steved•2/22/24, 4:37 PM

/subscribe D1

Tony LeungOP•2/22/24, 4:38 PM

not a good practice right...

TTony Leung btw I also tried a new index: ```sql CREATE INDEX project_tiktok_link_id_idx ON ...

Max (@rozenmd)•2/22/24, 4:38 PM

that's pretty much how you do it, yeah

Tony LeungOP•2/22/24, 4:38 PM

sorry I'm pretty new with SQLite

MMax (@rozenmd)that's pretty much how you do it, yeah

Tony LeungOP•2/22/24, 4:39 PM

sorry to bother you

hope you don't mind

Max (@rozenmd)•2/22/24, 4:39 PM

that's alright i learned SQL via postgres myself

Max (@rozenmd)•2/22/24, 4:39 PM

https://sqlite.org/queryplanner.html is a very good guide btw

Tony LeungOP•2/22/24, 4:39 PM

I'm just curious that why the expression

(tiktok_link IS NULL OR tiktok_link = '')

(tiktok_link IS NULL OR tiktok_link = '')

in index does not work like mysql

Tony LeungOP•2/22/24, 4:39 PM

can't seem to use it anywhere

Max (@rozenmd)•2/22/24, 4:40 PM

indexing by

tiktok_link

tiktok_link

covers that anyway

Tony LeungOP•2/22/24, 4:40 PM

thanks, I was overthinking

Tony LeungOP•2/22/24, 4:41 PM

i does not work as expected... this is much different from mysql

Tony LeungOP•2/22/24, 4:42 PM

i was nuking sqlite all the time... thanks for your support

MMax (@rozenmd)indexing by `tiktok_link` covers that anyway

Tony LeungOP•2/22/24, 4:44 PM

btw does using

indexed by

indexed by

reduces my rows read?

TTony Leung btw does using `indexed by` reduces my rows read?

Max (@rozenmd)•2/22/24, 4:53 PM

From what I understand of https://www.sqlite.org/lang_indexedby.html, only if the index you tell the query planner to use is more efficient

Max (@rozenmd)•2/22/24, 4:53 PM

It's not a hint, so it will ignore better indexes if you tell it

indexed by

indexed by

MMax (@rozenmd)It's not a hint, so it will ignore better indexes if you tell it `indexed by`

Tony LeungOP•2/22/24, 5:19 PM

thanks for clarification. I will check for rows read in d1 query meta

Advany•2/22/24, 6:07 PM

I have a array of 50k json objects. Is there any limit in d1 to insert that in one go?

And query those json objects, any limits on that?

Advany•2/22/24, 6:07 PM

I could not understand if this is limited during the beta

MMax (@rozenmd)From what I understand of https://www.sqlite.org/lang_indexedby.html, only if th...

Tony LeungOP•2/22/24, 6:49 PM

thanks, indexing like above drastically reduced my rows read, from ~400 to 10. It turns out that I don't really need

INDEXED BY

INDEXED BY

after all, sqlite is smart enough to choose the right index for me.

Tony LeungOP•2/22/24, 6:54 PM

so expression indexing does not help much for this situation (or does not work at all). I guess it's only needed when I have to query prefix match like

ABC%

ABC%

MMax (@rozenmd)From what I understand of https://www.sqlite.org/lang_indexedby.html, only if th...

F0rce•2/22/24, 7:05 PM

should the changes be included in the latest release

wrangler@3.29.0

wrangler@3.29.0

FF0rce should the changes be included in the latest release `wrangler@3.29.0`

Max (@rozenmd)•2/22/24, 7:05 PM

workerd !== wrangler

F0rce•2/22/24, 7:05 PM

jeah true, sorry

Max (@rozenmd)•2/22/24, 7:06 PM

https://github.com/cloudflare/workerd

GitHub

GitHub - cloudflare/workerd: The JavaScript / Wasm runtime that pow...

The JavaScript / Wasm runtime that powers Cloudflare Workers - cloudflare/workerd

F0rce•2/22/24, 7:06 PM

jeah had a typo in my head, sorry for pinging randomly.

MMax (@rozenmd)can you try accessing the DB again?

Sej•2/23/24, 1:55 AM

Hi, I can now access the DB. Thanks for the help

Tony LeungOP•2/23/24, 5:48 AM

that was quick, certified not :soontm: moment

JC•2/23/24, 3:51 PM

Hi, is there a way to export data from D1, sorta like a mysql dump?

JJC Hi, is there a way to export data from D1, sorta like a mysql dump?

Max (@rozenmd)•2/23/24, 3:54 PM

We're working on it - in the meanwhile there's https://github.com/nora-soderlund/cloudflare-d1-backups

GitHub

GitHub - nora-soderlund/cloudflare-d1-backups: A package to dump a ...

A package to dump a copy of your experimental D1 database through a worker. - nora-soderlund/cloudflare-d1-backups

MMax (@rozenmd)We're working on it - in the meanwhile there's https://github.com/nora-soderlund...

JC•2/23/24, 3:57 PM

Thanks!

Paul•2/23/24, 4:34 PM

I’m getting some pressure to move to Postgres so some people can use the admin dashboard tools like Looker. Any chance D1 works with any of those things?

Cyb3r-Jak3•2/23/24, 4:37 PM

Not unless they have added support for the HTTP API. D1 doesn’t have a standard connection string that others do

Paul•2/23/24, 4:45 PM

Dang

PPaul I’m getting some pressure to move to Postgres so some people can use the admin d...

volcanicislander•2/23/24, 4:54 PM

I'm in a similar situation. Trying to figure out how to connect with MS Power BI.

Vvolcanicislander I'm in a similar situation. Trying to figure out how to connect with MS Power BI...

Isaac McFadyen•2/23/24, 4:56 PM

The same answer applies here. Power BI doesn't have a D1 connector so you're out of luck there, unfortunately.

Isaac McFadyen•2/23/24, 4:56 PM

You could write a Worker that proxies requests to D1 but I'm not sure how Power BI's support for raw HTTP requests for data is.

IIsaac McFadyen You could write a Worker that proxies requests to D1 but I'm not sure how Power ...

volcanicislander•2/23/24, 4:57 PM

I was thinking about this as a workaround. Power BI can import data through an API endpoint

Isaac McFadyen•2/23/24, 4:58 PM

Then you could try that, yeah.

CCyb3r-Jak3 Not unless they have added support for the HTTP API. D1 doesn’t have a standard ...

Santosh•2/23/24, 5:55 PM

Is this not the reason that D1 cant compete with databases like Turso? I mean you would have to go via workers and a DO, which means theres a certain minimal latency. Is my understanding incorrect?

Santosh•2/23/24, 11:55 PM

Understood. But what I said is also correct as well? :lul:

Adi•2/24/24, 12:10 AM

Question about D1 bindings to Pages. I know that when you bind D1 to a worker it automatically restricts which region the worker spins up to be as close to D1 as possible.
Does it have the same behavior when you bind a pages project? If so, is there any way to get around that?

James•2/24/24, 4:17 AM

That behaviour only happens with smart placement, not by default

James•2/24/24, 4:17 AM

If you don’t want that behaviour, simply don’t enable smart placement

SSantosh Understood. But what I said is also correct as well? :lul:

Max (@rozenmd)•2/24/24, 8:18 AM

in theory if they had the same number of PoPs as us, and we had to go through Workers first and they didn't, yeah you'd gain 1-10ms of latency overhead on us

JJames That behaviour only happens with smart placement, not by default

Adi•2/24/24, 12:30 PM

ahh I see! That's great to hear. Thanks

Advany•2/24/24, 2:37 PM

I have a use case where I store a array of jsons and need to get them quickly for each customer.

I was thinking of storing that in a seperate table for each customer.

Is there a limit on amount of tables (or virtual tables) for d1?

Advany•2/24/24, 2:38 PM

And can will deletes and inserts (i dont do updates) of a table be automatically added to fts5 virtual table?

AAdvany I have a use case where I store a array of jsons and need to get them quickly fo...

K•2/24/24, 6:09 PM

You can do separate db for each customer. Basically you can create unlimited number of d1 DBs. IIRC you can store only 100 tables in one db.

Also, how big are those arrays? There is a limit of 1MB per row so maybe could just store them in row-per-customer.

KK You can do separate db for each customer. Basically you can create unlimited num...

Advany•2/25/24, 6:42 AM

That could be a idea. Let me investigate that!

btw I also tried a new index: ```sql CREATE INDEX project_tiktok_link_id_idx ON project (tiktok_link

Similar Threads

Similar Threads

Similar Threads