2
\$\begingroup\$

I have product results with some filter, and the one is category. I'll get the category list from product results. the model category and products is one to many relationship.

This is the search query:

$results = Product::where('available','=', 1)->whereRaw('stock_available > min_quantity');
$results = $results->where(function($query) use($search){
$query->where('name', 'LIKE', '%' . $search . '%')
->orWhere('sku', 'LIKE', '%' . $search . '%')
->orWhere('part_number', 'LIKE', '%' . $search . '%');
});

I try to get the category list from the results, like this:

$arrayCategory = $results->pluck('category_id');
$arrayType = $results->pluck('product_type_id');
$arrayManufactur = $results->pluck('manufacturer_id');
$arrayOem = $results->pluck('is_oem');
$arrayUsedStatus = $results->pluck('newused');

$resultCategory = array_unique($arrayCategory);
$arrayCountCategory = array_count_values($arrayCategory);
$categories = Categories::whereIn('id', $resultCategory)->with('products')->pluck('name', 'id');

$resultType = array_unique($arrayType);
$arrayCountType = array_count_values($arrayType);
$types = ProductType::whereIn('id', $resultType)->with('products')->pluck('name', 'id');

$resultManufacturer = array_unique($arrayManufactur);
$arrayCountManufactur = array_count_values($arrayManufactur);
$manufacturList = Manufactur::whereIn('id', $resultManufacturer)->with('products')->pluck('name', 'id');

$resultOem = array_unique($arrayOem);
$arrayCountOem = array_count_values($arrayOem);

$resultUsedStatus = array_unique($arrayUsedStatus);
$arrayCountUsedStat = array_count_values($arrayUsedStatus);

This code takes too much time loading, and I want to know how I can optimize it to achieve faster loading.

\$\endgroup\$
1
\$\begingroup\$

Your search query will not be able to leverage an index on the fields you are searching within. This is because you have a wildcard at the beginning of the search string and a default B-Tree index only works for prefix match use cases. So when the LIKE condition has a wild card at the beginning, you will need to perform a full table scan. Full table scans are REALLY bad from a performance standpoint.

It looks like you use case would work best with natural language full text search (MySQL link here - http://dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html ). This would require change to both your query and the database schema.

You also do a lot of individual single table queries here. My guess is that you can possibly optimize this by utilizing joins to get date across table with a single (or fewer queries) vs. reading data from one table, then another based on that result, then another based on that next result, etc.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.