wait that's 55 billion

Nnotjoemartinez wait that's 55 billion

H

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

H

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.

N

notjoemartinezOP•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?

N

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

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

N

notjoemartinezOP•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);

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

I

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

So kinda. Depends on your queries.

I

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

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

I

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

I

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

I

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.

I

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.

R

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?

H

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

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

H

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

G

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

I tried doing

PRAGMA foreign_keys=OFF

PRAGMA foreign_keys=OFF

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

G

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

G

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

To me it seems very inconsistent

G

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

H

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

N

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

use redis if you need smth really fast

NNob use redis if you need smth really fast

R

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 😉

N

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

you do redis replicas in us then

C

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?

C

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

i dont want to use prisma :<

K

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

@cloudflare/d1

@cloudflare/d1

@cloudflare/d1

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

K

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

C

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

ohhhh

C

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

thanks

B

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

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

M

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?

B

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

no where clause

B

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

the above is the whole query

M

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?

B

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

where would i check that?

Bbeau where would i check that?

M

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

B

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

yeah no such warnings

O

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

M

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

B

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

(ogshadowfiend is a coworker)

M

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

the dashboard console uses the HTTP API, which involves a roundtrip to a centralized DB on the US west-coast, it's not designed for performant querying just yet

O

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

Can def share it—what's the best channel to do that?

M

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

here's fine, they're not secrets

O

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

276296c7-7c25-4708-9d8e-b8c5ba0ff41f

O

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

(Of note, we're looking into this because we're getting big timeouts on the worker that's connecting to the db.)

B

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

~10k rows, the json representation is ~2mb

M

Max (@rozenmd)•6/17/24, 9:02 PM

how many requests per second are you sending your DB?

B

beau•6/17/24, 9:05 PM

B

beau•6/17/24, 9:06 PM

that seems high

Similar Threads

Similar Threads

Similar Threads