Hi guys,
so I ran into an issue, which I knew, was coming one day. I have a community application where I have a table for registered frontend users. I have stored all sorts of attributes in this table and we have fairly some users in that.
The software contains a search to search for other frontend users by various params like gender, age, country, language and more. The problem I have is, it is the same table as the authentication table or where the users store their settings so I run into performance issues when we have high load.
I was thinking so far to copy the relevant data into another table and keep the two tables in sync using observers.
Has anyone solved such an issue in an elegant way? Any thoughts on this approach? I was looking into switching to another DB but unfortunately there are external apps consuming this API so I have to return proper paginations. This makes it difficult to use DynamoDB or MongoDB for that.
Any thoughts / inputs are appreciated
I would move user settings out of the users table to a dedicated user settings table. I would also consider whether it'd make sense to index certain columns that you're searching on.
Another thing to consider is the queries themselves. The way a query is structured when doing joins and/or subqueries can make a huge difference in performance.
@idew I was thinking the same regarding the extraction.
Then again I think it is easier to extract the searchable attributes, means less to adapt for me.
Yes for the structuring I am aware, with debugbar I could get decent results.
@ChristophAust Have you looked into the MySQL config side of things? If the DB is using InnoDB, you could consider resizing your buffer pool - https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html
Like @idew suggests, you should move all the columns from the users table that is not required for authentication to a user profile table (maybe even several). Then things that can contain less than a handful of values, like gender can be made into a dropdown or checkboxes. Fields that are foreign keys might need to have their own search element as well. All the text fields can then be added into a full text index, which can then be easily searched.
If you really starting to grow and you need a faster and more robust search engine, take a look at Elasticsearch. The initial learning curve is a bit steep, but there are many tutorials around.
@hondnl yes, I have thought about that. The constraint I have though is that I cannot change the output towards the API, so the schema of my returned JSON must be the same. And me being lazy I just used the pagination method. This does not exist on elasticsearch, right?
Using https://laravel.com/docs/9.x/scout you can kinda emulate laravel methods. I see that laravel scout supports Meilisearch , which is starting to look like a good alternative for Elasticsearch if your search requests are not too elaborate.
Please or to participate in this conversation.