efficiency of: return (await ctx.db.query("messages").collect()).length;
that does not seem very efficient if I had 1mil rows in a table.
Two suggestions/questions:
1. Is there a count of a table (without scanning the whole table)?
2. Alternatively, should there not a some for of projection?
What if I had a table with 50 columns and I need a list that really only contains 2 or 3 of the columns?
Am I going to get all 1M rows streamed into memory or somehow in order to get a count?
6 Replies
I see https://github.com/get-convex/convex-backend/issues/10 mentions a undocumented feature.
Count is quite integral in data management in my view.
GitHub
How to get the count (length) of a table · Issue #10 · get-convex/c...
Hey I'm evaluation convex for an upcoming project and I need to count all entries in a table (or by index). There doesn't seem to be a dedicated api function, collect() doesn't work for...
Agreed, counting by collecting is inefficient and using
count is only semi-supported. For now people have been denormalizing the count, like in this video:
https://youtu.be/LRUWplYoejQ?si=-fvZkj1CePRIxp9R
(there's an interesting bit ~1 hour in where he talks about how to make it more efficient).
We have an easier way to calculate counts coming soon, stay tunedWeb Dev Cody
YouTube
Rendering ONE MILLION buttons on a single page (with realtime updates)
One million chests! https://millionchests.webdevcody.com/
Be sure to checkout convex which helped making this site a breeze. https://convex.dev/c/wdc
Code is here https://github.com/webdevcody/million-chests
00:00:00 intro
00:02:00 Setup
00:06:13 Display 100 Chests
00:11:49 Opening a Chest
00:23:48 Coupon Code
00:29:18 Gold Chest
00:46:09 Co...
any ideas and plans on field/column projections in a query @lee ,
lists often don't need all of the underlying columns
If you don't want to fetch some columns, they should be in a separate table and you can do a join ( @jamwt described this best) . If all you want is the count, you can store the denormalized count and then you don't need to fetch any fields/columns
Thx. @lee , "separate table" that's a surprising answer.
To me it seems that convex will not be suitable for administration systems where tables have 40+ columns or subObjects (json) that is unique to the record being stored. (Surely you don't want a ton of 1-1 relationships?)
Think of an event, a patient admission, a treatment etc. (Chats and tasks are really basic examples)
So if I want to lookup/search for a name or a field in a large table, you are going to load all the data into memory first, and then let me use array mapping to filter out the columns and send it to the client.
It seems there are only "select * from table" or collection.find(qry, {"no projection"}) options in convex.
Could you confirm that there is no "select col1, col4, col9 from largeTable" equivalent (even on the server)?
If not, do you consider it important, or does it just not matter.
(pardon the persistence, but maybe it's the lessons learned since the early 90's using 4GL / SQL and recently mongo that makes me think about optimisation of data to memory.)
btw here's the full context https://discord.com/channels/1019350475847499849/1019350478817079338/1295432256747339858