what if `id` is first in your index?

MMax (@rozenmd)what if `id` is first in your index?

T

Tony Leung•2/22/24, 4:32 PM

sorry, my actual query is:

SELECT ... FROM project WHERE id < '01HQ81GRDX539N2EJSW1ECGRMY' AND (tiktok_link IS NULL OR tiktok_link = '')

SELECT ... FROM project WHERE id < '01HQ81GRDX539N2EJSW1ECGRMY' AND (tiktok_link IS NULL OR tiktok_link = '')

SELECT ... FROM project WHERE id < '01HQ81GRDX539N2EJSW1ECGRMY' AND (tiktok_link IS NULL OR tiktok_link = '')

SELECT ... FROM project WHERE id < '01HQ81GRDX539N2EJSW1ECGRMY' AND (tiktok_link IS NULL OR tiktok_link = '')

T

Tony Leung•2/22/24, 4:33 PM

i've tried swapping the position of

id

id

and the expression

(tiktok_link IS NULL OR tiktok_link = '')

(tiktok_link IS NULL OR tiktok_link = '')

(tiktok_link IS NULL OR tiktok_link = '')

(tiktok_link IS NULL OR tiktok_link = '')

T

Tony Leung•2/22/24, 4:33 PM

both in the query and the index

TTony Leung sorry, my actual query is: ```sql SELECT ... FROM project WHERE id < '01HQ81GRDX...

M

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

it's the less than

T

Tony Leung•2/22/24, 4:33 PM

but none of them work

T

Tony Leung•2/22/24, 4:34 PM

i've tried

SELECT ... FROM project WHERE (tiktok_link IS NULL OR tiktok_link = '') and id < '01HQ81GRDX539N2EJSW1ECGRMY'

SELECT ... FROM project WHERE (tiktok_link IS NULL OR tiktok_link = '') and id < '01HQ81GRDX539N2EJSW1ECGRMY'

SELECT ... FROM project WHERE (tiktok_link IS NULL OR tiktok_link = '') and id < '01HQ81GRDX539N2EJSW1ECGRMY'

SELECT ... FROM project WHERE (tiktok_link IS NULL OR tiktok_link = '') and id < '01HQ81GRDX539N2EJSW1ECGRMY'

i do aware that sqlite

id <

id <

id <

id < will not use other columns in the index after that

T

Tony Leung•2/22/24, 4:35 PM

I also tried

INDEXED BY

INDEXED BY

INDEXED BY

INDEXED BY, it does not work neither

M

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

because you're asking sqlite to scan your table

T

Tony Leung•2/22/24, 4:36 PM

oh...

T

Tony Leung•2/22/24, 4:36 PM

so the expression

id <

id <

id <

id < will make sqlite scan the table?

T

Tony Leung•2/22/24, 4:37 PM

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

S

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

/subscribe D1

T

Tony Leung•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 ...

M

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

that's pretty much how you do it, yeah

T

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

sorry I'm pretty new with SQLite

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

T

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

sorry to bother you

hope you don't mind

M

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

that's alright i learned SQL via postgres myself

M

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

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

T

Tony Leung•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 = '')

(tiktok_link IS NULL OR tiktok_link = '')

(tiktok_link IS NULL OR tiktok_link = '') in index does not work like mysql

T

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

can't seem to use it anywhere

M

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

indexing by

tiktok_link

tiktok_link

tiktok_link

tiktok_link covers that anyway

T

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

thanks, I was overthinking

T

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

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

T

Tony Leung•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

T

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

btw does using

indexed by

indexed by

indexed by

indexed by reduces my rows read?

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

M

Max (@rozenmd)OP•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

M

Max (@rozenmd)OP•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

indexed by

indexed by

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

T

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

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

A

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?

A

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...

T

Tony Leung•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

INDEXED BY

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

T

Tony Leung•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%

ABC%

ABC%

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

F

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

should the changes be included in the latest release

wrangler@3.29.0

wrangler@3.29.0

wrangler@3.29.0

wrangler@3.29.0

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

M

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

workerd !== wrangler

F

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

jeah true, sorry

M

Max (@rozenmd)OP•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

F

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?

S

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

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

T

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

that was quick, certified not :soontm: moment

J

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?

M

Max (@rozenmd)OP•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...

J

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

Thanks!

P

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?

C

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

P

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...

V

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...

I

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.

I

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 ...

V

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

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

I

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

Then you could try that, yeah.

what if `id` is first in your index?

Similar Threads

Similar Threads

Similar Threads