Level 75
Many times I add a search above table:
I have search by date, amount, description.
Clicking one of the searches displays a hidden search box. It's css is display:none til needed.
Feb 10, 2021
14
Level 5
Search Table
Hello guys,
I am reaching out for some best practice. So I have basically a usertable where user data (mail, password, timestamps, etc.) is stored. In the app I have some community functionality where you can search this exact table by zip-code, gender, name , etc. and even by some relations data.
I somehow have the feeling this is not best practice. I have not really bad experience or issues with that other than some deadlocks, which result from updateing the "updated_at" on login so I can sort by that.
I am afraid of running into more issues and maybe performance problems.
So I thought of two methods how to solve this:
- create a synchronized mysql table with all necessary data as a flat table and query this one for the search
- create an elasticsearch or dynamodb table, keep this in sync and query this (I have no experience with neither yet)
I prefer the first, since I am more familiar with mysql and I need pagination, but somehow I do not have the "yay" feeling on any of the solutions.
Did any of you guys face such an issue and what was your decision and why?
Thank you in advance.
Level 5
Thank you for your reply. It does not answer my question though. Or am I missing something?
Level 16
Databases are made for storing data, not searching. There are search engines that solve the latter such as https://github.com/meilisearch/meilisearch-laravel-scout
You can get away with simple queries using the DB layer, but as soon as they get a little bit more complex, things tend to get out of hand really fast.
It all depends on your use case though, you might be better off using a materialized view such as you have suggested, but tbf I'd rather use the right tool instead.
Level 5
So in AWS context would you suggest elasticsearch over dynamodb?
Level 16
That's like comparing apples with oranges. DynamoDB is a NoSQL database whereas Elastic is a proper search engine. Therefore the only right tool here is Elastic, but Elastic is notoriously hard to properly set up. Highly recommend Babenkoivan's packages if you go with Elastic.
Level 5
Thank you, I will try it out.
Level 5
I just saw it supports pagination so I think this is my way to go.
Thank you!
Level 75
@christophaust I have never had problems with:
$search_term = $search_term . "%";
$query = yourmodel::where('your_field', 'like', $search_term)
other search tearms as needed.
In other words using mysql to search. But elasticsearch is supposed to be good.
I have apps that you can find an old invoice via customer last name or date, or PO number, etc.
A lot has to do with your DB structure. But only suggestions here, nothing else.
Level 5
But I run into some issues. For example I perform a search query on it while the timestamps are being updated. So I run into a deadlock.
Level 5
Have you ever worked with synchronizing a table into a search table or so?
Level 75
You're running multiple transactions?
Level 5
I have a stateless restful api so yes
Level 75
The database synchronized option would work, but I'd say find a couple of good tutorials on it and elasticsearch. Then decide, there may even be some Youtube videos.
I have never had to do synchronization. I have heard of a parallel index technique, usually in huge databases.
I see here on forum a lot of API questions, but I haven't seen one with the deadlock problem while performing a search.
1 like
Level 5
Yes, I would consider this project rather huge, so I think I will consider elasticsearch. Thank you!
Please or to participate in this conversation.