error when querying on big database 300 million rows.

Hi,

When trying to query using 'Query.contains' on string attribute containing 300 million rows of data im getting this error:

AppwriteException: , ClientException with SocketException: The semaphore timeout period has expired.
(OS Error: The semaphore timeout period has expired.
, errno = 121)

What are the fixes? Do making a fulltext index help or not?

Can you provide more context?
What setup you have? self-hosted or cloud?
What's the data structure?
What's the query you're trying to run?
What permissions level do you use for the collection?

Selfhosted,
The data structure is a:
row for phone numbers,
and a row for names,

The query is Query.contains('name', ''

The permission is 'any' have all permission.

I have an key index in the numbers row, and a key index in the name row.

How much time in avg it usually takes this query to complete?

It never completed not ones, i always get this error when querying the name row, but with the numbers row it fast,
I don't get errors in the numbers row querying

and where did you get the The semaphore timeout period has expired. error? Appwrite 500 response or in the console logs?

The error happens when the query contains two name like this "john doe"

Are you able to see the elements in the console?

Yes the development mode is enabled

No

Try adding an index for $createdAt

It is indexed by default by appwrite

The row id and _uid and created at and updated at is indexed by default.

So... a few things I have in mind.

1. Contains is not a super performant query because its hard to index properly. This is still great for many use cases, but in a table that big I can see how it can take a lot of time as it can't use any index efficiently.

2. I would try a simpler query operator like equals to try and ensure no other problem exists. With your index it should be fairly fast even on a big table.

3. For finding the name with a loose search, fulltext index should work better. The downside is that it will take a big chunk of storage space, but with this size of table it won't be avoidable.

4. Another thing I would try is to go to collection level permissions instead of document level, collection level will be faster at scale.

@jesus also, I sent you a DM, I'm very curious to learn more on your use case with Appwrite:)

For 3 with the full text index, you should use the search operator to take advantage of the index.

Yes it is collection level permission,

And i have tried the fulltext index i get the same time out error

Does a simple query work?

Yes but i want to search between the names example:

John,
John doe,
Jeusu john doe,

I want the query to grab all these, the only solution is contains and search and all of them get time out error.

I also wonder if this error comes from Appwrite or your client. From looking online it seems like an error a Flutter client could throw

No, I'm using sentry and I'm getting the same error. In the logs

Just diffrent log text

Can you DM me with the logs information?

Ok just a minute