Level 20
Apr 6, 2018
10
Level 4
Query perform very slow when using where in condition
I am having this query.
$items = DB::table('items')
->leftJoin('default_price', 'items.id_item', '=', 'default_price.id_item')
->leftJoin('vendors', 'items.id_vendor', '=', 'vendors.id_supplier')
->whereIn('items.id_vendor', $vendors)
->whereIn('items.id_item', $id_items_default)
->get()->toArray();
There's around 90 vendors and 10k items.
->whereIn('items.id_item', $id_items_default)
This condition caused whole performance slow down about 20 sec. How can i make it perform faster even with huge data? Thanks in advance.
Level 4
@Nash Yes i already using index.
Level 122
What indexes do you have?
Level 4
@Snapey for all the id
Level 20
Are there 10k items in your table or are you trying to fetch 10k items at once? If you are doing the latter, you might want to paginate the query.
What is $id_items_default? Could the defaults be stored in a separate table and help fetch only matching items via an inner or right join?
join('defaults', 'defaults.id_item', '=', 'items.id_item')
Level 122
You need to index any column that will be used for a search. So, item_id should be indexed.
Any column that appears in a where statement, and will be used frequently should be indexed
Level 4
@Nash $id_items_default is the items get from another table, where only return id_item.
Level 4
After i get the items return from query i am using lengthawarepaginator to show my items.
Level 20
If the default id's are available in another table, then you could do an inner join to get only matching rows (see my previous reply), there should be no need for a separate whereIn. You should also limit/paginate the actual query. There is no point (peformance wise) in adding pagination after you've already fetched 30k items from the db using ->get()
Level 4
Try Snapey's suggestion. Furthermore, are you using DebugBar extension? Check how much queries are being executed after this join. This can be an issue solved by eager loading.
Please or to participate in this conversation.