Hello!
I'm seeking assistance regarding a large database containing approximately 2 million products. Each product has unique_id, name, product_type, and other attributes. I have attempted to implement a search function for these products.
Here's the code I'm currently using:
Product::where('name', 'like', '%' . $search . '%')->orderBy($sort, $sortDirection)->simplePaginate(20);
However, I've noticed that when using the orderBy clause, the search time increases by approximately 80%, which significantly affects the performance. I've come across suggestions recommending the indexing of certain columns to optimize and improve search speed. Could you please explain how indexing works and which columns should be indexed?
I've observed that when searching for a specific term that I know only one product has, the search time increases dramatically. On the other hand, if I search for a well-known term that many products contain, the search speed is considerably faster, and the response time is reduced.
I would greatly appreciate any suggestions or feedback on how to implement a more efficient search function. Thank you in advance!
