dhakan Comments - Answer Overflow

dhakan

Posts Comments

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

I learnt as well! Thanks a bunch for the back and forth. Really appreciated your help.

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

@Raphaël M (@rphlmr) ⚡ this is an improvement of astronomical proportions

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

I must have overlooked one important detail: FK:s are NOT automatically indexed. I didn't actually know this. After adding the FK index to timestamps.video_id these are the results:

Query: select "id", "source_id", "name", "description", "duration", "thumbnail", "created_at", "updated_at", (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") as "timestampsCount" from "videos" where "timestampsCount" > ? order by "videos"."created_at" desc limit ? -- params: [0, 12]
selectVideos: 3.937ms

Query: select "id", "source_id", "name", "description", "duration", "thumbnail", "created_at", "updated_at", (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") as "timestampsCount" from "videos" where "timestampsCount" > ? order by "videos"."created_at" desc limit ? -- params: [0, 12]
selectVideos: 2.905ms

Query: select "id", "source_id", "name", "description", "duration", "thumbnail", "created_at", "updated_at", (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") as "timestampsCount" from "videos" where "timestampsCount" > ? order by "videos"."created_at" desc limit ? -- params: [0, 12]
selectVideos: 2.351ms

Query: select "id", "source_id", "name", "description", "duration", "thumbnail", "created_at", "updated_at", (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") as "timestampsCount" from "videos" where "timestampsCount" > ? order by "videos"."created_at" desc limit ? -- params: [0, 12]
selectVideos: 3.937ms

Query: select "id", "source_id", "name", "description", "duration", "thumbnail", "created_at", "updated_at", (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") as "timestampsCount" from "videos" where "timestampsCount" > ? order by "videos"."created_at" desc limit ? -- params: [0, 12]
selectVideos: 2.905ms

Query: select "id", "source_id", "name", "description", "duration", "thumbnail", "created_at", "updated_at", (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") as "timestampsCount" from "videos" where "timestampsCount" > ? order by "videos"."created_at" desc limit ? -- params: [0, 12]
selectVideos: 2.351ms

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

Someone speculated that my deployed variant being slower might be Fly machine config (shared cpu instead of performance), but that doesn’t explain the 130 ms one locally…

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

I’m going to try to see what happens without drizzle

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

Something seems very off here

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

Thanks Raphael. I’ve been talking to people in the Fly community, and I got an answer like this:

litefs just primarily handles the global replication + write node to primary right? If you're reading from your local sqlite file, it should take microseconds for simple queries and single digit ms for complex ones. I have queries that join/filter/select json props and they average ~5ms.

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

Great! Is it any quicker?

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

Will have to investigate that

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

Really curious how the deployed version of identical data takes 3 times longer

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

I’ll try that when I get back home!

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

Oooh you’re using the extras field?

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

Hmmm, looks funky indeed!

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

And the times printed above are from the ORM typescript code

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

My db is running on a mounted volume in what’s called LiteFS. So every query runs on the same machine

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

(also with a limit of 12)

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

Seems like these queries when running in production on Fly (same dataset) takes 350-400 ms 😦

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

(default logging on localhost)

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

Does this help?

Query: select "id", "source_id", "name", "description", "duration", "thumbnail", "created_at", "updated_at", (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") as "timestampsCount" from "videos" where (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") > ? order by "videos"."created_at" desc limit ? -- params: [0, 12]
selectVideos: 158.676ms

Query: select "id", "source_id", "name", "description", "duration", "thumbnail", "created_at", "updated_at", (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") as "timestampsCount" from "videos" where (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") > ? order by "videos"."created_at" desc limit ? -- params: [0, 12]
selectVideos: 117.11ms

Query: select "id", "source_id", "name", "description", "duration", "thumbnail", "created_at", "updated_at", (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") as "timestampsCount" from "videos" where (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") > ? order by "videos"."created_at" desc limit ? -- params: [0, 12]
selectVideos: 113.088ms

Query: select "id", "source_id", "name", "description", "duration", "thumbnail", "created_at", "updated_at", (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") as "timestampsCount" from "videos" where (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") > ? order by "videos"."created_at" desc limit ? -- params: [0, 12]
selectVideos: 158.676ms

Query: select "id", "source_id", "name", "description", "duration", "thumbnail", "created_at", "updated_at", (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") as "timestampsCount" from "videos" where (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") > ? order by "videos"."created_at" desc limit ? -- params: [0, 12]
selectVideos: 117.11ms

Query: select "id", "source_id", "name", "description", "duration", "thumbnail", "created_at", "updated_at", (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") as "timestampsCount" from "videos" where (select count("id") from "timestamps" where "timestamps"."video_id" = "videos"."id") > ? order by "videos"."created_at" desc limit ? -- params: [0, 12]
selectVideos: 113.088ms

147 replies

DTDrizzle Team

•Created by dhakan on 7/24/2024 in #help

SQLite 'WHERE table.relationship (one to many) > 0'

but it's better than 300 that I had

147 replies

Gaming

Programming