Cloudflare Developers•2y ago

got a fix up: <https://github.com/cloudflare/workers-sdk/pull/6062>

got a fix up: https://github.com/cloudflare/workers-sdk/pull/6062

WWalshy got a fix up: <https://github.com/cloudflare/workers-sdk/pull/6062>

Nob•6/15/24, 7:23 PM

is d1 replicated yet ?

Nob•6/15/24, 7:23 PM

or its still one single primary db atm

NNob or its still one single primary db atm

Hello, I’m Allie!•6/15/24, 7:31 PM

One DB Instance

notjoemartinez•6/15/24, 10:02 PM

I'm having trouble understanding my d1 bill. If it's a formatting problem and the number is

55,835,335.95

55,835,335.95

558,353,359.5

558,353,359.5

how do I read a fraction of a row? Also if first 25 billion included rows is included is the real number

25 billion

25 billion

55,835,335.95

55,835,335.95

? What's the number under the

55,835,335,95

55,835,335,95

, does that mean I uses

55,835,335,95 x 3

55,835,335,95 x 3

Nnotjoemartinez I'm having trouble understanding my d1 bill. If it's a formatting problem and th...

Hello, I’m Allie!•6/15/24, 10:06 PM

That looks like 55,835,335,953 to me

Hello, I’m Allie!•6/15/24, 10:06 PM

It appears the 3 got wrapped to the next line because it ran out of space

notjoemartinez•6/15/24, 10:07 PM

Okay but would it still be

25 billion + 55,835,335,953

25 billion + 55,835,335,953

WalshyOP•6/15/24, 10:08 PM

25 bil is included in the number, so it's actually 30 billion billable rows

notjoemartinez•6/15/24, 10:08 PM

wait that's 55 billion

Nnotjoemartinez wait that's 55 billion

Hello, I’m Allie!•6/15/24, 10:11 PM

Hello, I’m Allie!•6/15/24, 10:13 PM

Or https://www.wolframalpha.com/input?i=Absolute+Value+of+%28%2855835335953+minus+25+billion%29+times+0.001+per+million%29

Absolute Value of ((55835335953 minus 25 billion) times 0.001 per m...

Wolfram|Alpha brings expert-level knowledge and capabilities to the broadest possible range of people—spanning all professions and education levels.

notjoemartinez•6/15/24, 10:14 PM

thanks for the clarification! so if I have a full text search table with a million rows, does that mean every time I run a search I'm using a million reads?

notjoemartinez•6/15/24, 10:14 PM

Ppeshakoo to my knowledge, unless you filter by primary key or use [indexes](https://devel...

notjoemartinez•6/15/24, 11:03 PM

Doesn't sqlite fts add indexes by default? This is how I'm making the schema:

CREATE TABLE IF NOT EXISTS transcripts (
    feed_id INTEGER REFERENCES feeds_metadata(feed_id),
    archive_date TEXT,
    archive_date_time TEXT,
    object_key TEXT,
    segment_start REAL,
    segment_end REAL,
    segment TEXT
);

CREATE VIRTUAL TABLE IF NOT EXISTS transcripts_fts USING fts5(segment);

CREATE TABLE IF NOT EXISTS transcripts (
    feed_id INTEGER REFERENCES feeds_metadata(feed_id),
    archive_date TEXT,
    archive_date_time TEXT,
    object_key TEXT,
    segment_start REAL,
    segment_end REAL,
    segment TEXT
);

CREATE VIRTUAL TABLE IF NOT EXISTS transcripts_fts USING fts5(segment);

Nnotjoemartinez Doesn't sqlite fts add indexes by default? This is how I'm making the schema: ``...

Isaac McFadyen•6/15/24, 11:06 PM

So kinda. Depends on your queries.

Isaac McFadyen•6/15/24, 11:06 PM

The table you'll need to add indexes on yourself.

Isaac McFadyen•6/15/24, 11:07 PM

The virtual table has certain default indexes applied. But if you're querying for a prefix you'll need to add an explicit prefix index: https://sqlite.org/fts5.html

Isaac McFadyen•6/15/24, 11:07 PM

By default, FTS5 maintains a single index recording the location of each token instance within the document set. This means that querying for complete tokens is fast, as it requires a single lookup, but querying for a prefix token can be slow, as it requires a range scan. For example, to query for the prefix token "abc*" requires a range scan of all tokens greater than or equal to "abc" and less than "abd".

Isaac McFadyen•6/15/24, 11:07 PM

A prefix index is a separate index that records the location of all instances of prefix tokens of a certain length in characters used to speed up queries for prefix tokens.

Isaac McFadyen•6/15/24, 11:08 PM

I expect you're seeing the result of that there with the

MATCH ?

MATCH ?

depending on what the substitution is.

Ruurtjan•6/16/24, 12:38 PM

I’m working on a domain name availability checker that is faster than you can type. I’ve got access to a database of all domain names that are in use, updated daily.

As I understand, D1 currently doesn’t have global read replicas, but it’s been in the works for a while. Is there a private alpha I can join?

Hannes•6/16/24, 1:23 PM

I currently have trouble (again) importing my database. The SQL file is about 18 MB. I cannot import it directly because of this issue https://github.com/cloudflare/workers-sdk/issues/5683, but also after sorting all CREATE TABLE commands to the top of the file, it fails with

internal error

internal error

... A local import works fine. Any idea what to try?

RRuurtjan I’m working on a domain name availability checker that is faster than you can ty...

Hello, I’m Allie!•6/16/24, 1:23 PM

Afaik it isn’t available in any capacity atm

HHannes I currently have trouble (again) importing my database. The SQL file is about 18...

Gerbuuun•6/16/24, 6:48 PM

I tried doing

PRAGMA foreign_keys=OFF

PRAGMA foreign_keys=OFF

and that made it work somehow. (even though it is not supported?) Idk if that fixed it but you could at least try

Gerbuuun•6/16/24, 6:48 PM

I don't really know how foreign keys work in D1. At least not as described in the SQLite docs

Gerbuuun•6/16/24, 6:49 PM

To me it seems very inconsistent

Gerbuuun•6/16/24, 6:49 PM

Or a recent update broke something

GGerbuuun I tried doing `PRAGMA foreign_keys=OFF` and that made it work somehow. (even tho...

Hannes•6/16/24, 6:56 PM

Interesting. Did not work for me, unfortunately. Thanks for the idea, though

RRuurtjan I’m working on a domain name availability checker that is faster than you can ty...

Nob•6/17/24, 12:13 AM

use redis if you need smth really fast

NNob use redis if you need smth really fast

Ruurtjan•6/17/24, 4:50 AM

0ms response in Europe will still be 100-200 in the US

RRuurtjan 0ms response in Europe will still be 100-200 in the US 😉

Nob•6/17/24, 7:25 AM

you do redis replicas in us then

check engine•6/17/24, 7:10 PM

I'm new to D1, @cloudflare/d1 is deprecated on npm. What should i use with node.js?

check engine•6/17/24, 7:12 PM

i dont want to use prisma :<

kian•6/17/24, 7:29 PM

@cloudflare/d1

@cloudflare/d1

was always for Workers, not NodeJS - it's just replaced by a built-in equivalent now.

kian•6/17/24, 7:29 PM

The only way to use D1 outside of Workers is the HTTP API, and I don't believe Cloudflare offer any wrappers around that.

Kkian `@cloudflare/d1` was always for Workers, not NodeJS - it's just replaced by a bu...

check engine•6/17/24, 7:40 PM

ohhhh

check engine•6/17/24, 7:40 PM

thanks

beau•6/17/24, 8:46 PM

howdy! i have a database with ~10k deposits. I'm running a query like:

SELECT
  deposits.id,
  deposits.address,
  deposits.amount,
  deposits.token
FROM deposits

SELECT
  deposits.id,
  deposits.address,
  deposits.amount,
  deposits.token
FROM deposits

and it's taking ~30+ seconds. anyone have any clue about what's happening or how we could speed it up?

i've tried cloning my remote data locally and i'm getting similar performance

Bbeau howdy! i have a database with ~10k deposits. I'm running a query like: ``` SELEC...

Max (@rozenmd)•6/17/24, 8:50 PM

do you have a WHERE clause in that query, and do you have indexes on what you're looking for in the WHERE clause?

beau•6/17/24, 8:50 PM

no where clause

beau•6/17/24, 8:50 PM

the above is the whole query

Max (@rozenmd)•6/17/24, 8:52 PM

and when you visit the cloudflare dashboard, there's no warnings about it being an alpha DB, right?

beau•6/17/24, 8:52 PM

where would i check that?

Bbeau where would i check that?

Max (@rozenmd)•6/17/24, 8:53 PM

beau•6/17/24, 8:53 PM

yeah no such warnings

ogshadowfiend•6/17/24, 8:55 PM

Some additional data here… I ran the same query on the Cloudflare console two different times; in both cases the query says it executes in 20-40ms, but the response time is two orders of magnitude larger in one case and 3 orders of magnitude larger in another.

Bbeau yeah no such warnings

Max (@rozenmd)•6/17/24, 8:55 PM

can you share your database id? it's well past the end of my day here but I can look into it tomorrow morning

beau•6/17/24, 8:55 PM

(ogshadowfiend is a coworker)

CREATE TABLE IF NOT EXISTS transcripts ( feed_id INTEGER REFERENCES feeds_metadata(feed_id), archive_date TEXT, archive_date_time TEXT, object_key TEXT, segment_start REAL, segment_end REAL, segment TEXT ); CREATE VIRTUAL TABLE IF NOT EXISTS transcripts_fts USING fts5(segment);

got a fix up: <https://github.com/cloudflare/workers-sdk/pull/6062>

Similar Threads

Similar Threads

Similar Threads