Hi guys, I'm working on a project with a lot of data. I'm having a hard time getting a query result within 5 seconds. One table(warranty) has almost 4,000,000 rows.
One example function I'm doing is a keyup function for product search function for the logged in user :
Pass input data via ajax get request.
Run this query:
ItemWarranty::where('user_id', request()->user()->id) //get logged user
->where('product_no', "LIKE","%{$request->input('query')}%") //get user input
->groupBy('product_no') //if there are multiple records w/ the same product no
->orderBy('serial_no', 'asc') //change order
->limit(10) //get only 10 from the 4,000,000
->pluck('product_no'); //will only get data from that column
Display the results in a dropdown list so the user can choose
I have indexed the table (warranty) for the "product_no" column, optimized cache, and used sphinx as well. The server's performance is not so bad as well.
The fastest processing time I could get is ~20sec. The usual takes up to ~40-60seconds. I've talked to the client about the database(they provide it), but it's no use.
I'm at my wit's end and I would like to hear suggestions from you guys.
I had exactly the same issue when searching products/sites table (i.e. each site has many products). I had 16 sites and ~1.5 million products. Initially I was stuck at 30 seconds, which is no usable. I tried:
Indexing various tables, this brought the time down to 10 seconds, again far too long
Creating an optimized table for each site, that consisted of a product id and title (the column I wished to search upon). This knocked another 5 seconds from the time, but was far from ideal
Then I came across the excellent TNT Search Scout Driver, that brought the query down to < 3 seconds. This is so fast because it creates an optimized search index that is a flat sqlite file. You get all the benefits of speed and fuzzy matching, typos, did you mean functionality etc.
If you're querying using Mysql's "LIKE" operator, that is basically the slowest way to search. For your query, I'd recommend using the select($cols) function so columns that are not required are never queried or returned. I believe pluck() will simply filter the already returned collection down to a single column.
@chireen I'm working in a project that contain more than 500cr data in a table and it's works fine. Only thing you need to check is how optimized your table is. you should index the columns that you need to check.
Once the table is optimized I prefer query builder over Eloquent( I felt query builder is faster from my experience). And try to do an "EXPLAIN" operation to check what causing the query to take much time. Mostly "GROUP BY" and "ORDER BY" will slow down the query. In this case I suggest you to get the result and do the rest operations in PHP. it'll save your 90% time. :)
//FYI: you can cache the results with same parameters and get the cached data when requested next time.
@jestins I have tried your suggestion on doing the grouping and sorting operations in php. The results knocked down ~50% of the search time. I am now down to 5-10sec per keyup.
@Robstar I applied the select('col') along with the removal of orderBy & groupBy from the query and it reduced the search execution significantly.
I've also checked the toSearchableArray() function and I'm not really sure what to put here. For now this is what's in the function:
Can you try query builder instead of Eloquent.? and make sure if any join is thr then both table should be indexed. and paste your EXPLAIN query result here
Hi everyone, I apologize for the very late reply. I would just like to inform you guys that everything is working swiftly now!
@jestins I encountered problems manually sorting and grouping the esults when I used the query builder. I don't quite get how it gives out the results.
