Level 102
Well first of all. Indexes. Are you sure you indexing optimally for the queries being run? Make some benchmarks with dummy data or use explain to see how the query is performed
Feb 14, 2022
2
Level 1
Query by relations and problem with whereHas speed
I have models Product, Property, PropertyValue and product_property_value intermediate table for Many to Many relation with structure: product_id property_id When products fiters are in action, filter generate query builder like this
Product::query()
->whereHas('propertyValues', function ($query) {
$query->where('property_id', 'some_property_id');
$query->whereIn('value', ['red', 'blue']);
})
->whereHas('propertyValues', function ($query) {
$query->where('property_id', 'some_property_id');
$query->whereBetween('value', [20, 1000]);
})
->whereHas('propertyValues', function ($query) {
$query->where('property_id', 'some_property_id');
$query->where( 'value', 'usa');
});
It works properly but query can takes up to 1-10 seconds when many different filters are selected. I think it happened because query like this generates many joins in table with thousands items. ( Every whereHas subquery generate one join ) like this:
... and exists (select * from `property_values` inner join `product_property_value` on `property_values`.`id` = `product_property_value`.`property_value_id` where `products`.`id` = `product_property_value`.`product_id` and `property_id` = ?...
I found similar problems and there are well explained in this video https://www.youtube.com/watch?v=JOnXX-N96NE So the question is - how can i solve this issue??? Because it is trivial task for e-commerce. Maybe there is some trick with query without whereHas? In video developer replace whereHas with select trick but in my case I need to search not only ids, but it may be whereIn and array or even range or something else... Any ideas ?
Level 1
@Sinnbeck This toSql()
select * from `products` where (exists (select * from `property_values` inner join `product_property_value` on `property_values`.`id` = `product_property_value`.`property_value_id` where `products`.`id` = `product_property_value`.`product_id` and `property_id` = ? and (`integer` in (?)))) and (exists (select * from `property_values` inner join `product_property_value` on `property_values`.`id` = `product_property_value`.`property_value_id` where `products`.`id` = `product_property_value`.`product_id` and `property_id` = ? and (`double` between ? and ? or `double` between ? and ? or `double` between ? and ? or `double` between ? and ? or `double` between ? and ? or `double` between ? and ?))) and (exists (select * from `property_values` inner join `product_property_value` on `property_values`.`id` = `product_property_value`.`property_value_id` where `products`.`id` = `product_property_value`.`product_id` and `property_id` = ? and `string` in (?, ?)))
this explane
0 => {#3724 ▼
+"id": "1"
+"select_type": "PRIMARY"
+"table": "property_values"
+"type": "index_merge"
+"possible_keys": "PRIMARY,property_values_property_id_index,property_values_integer_index"
+"key": "property_values_integer_index,property_values_property_id_index"
+"key_len": "9,8"
+"ref": null
+"rows": "1"
+"Extra": "Using intersect(property_values_integer_index,property_values_property_id_index); Using where; Using index; Start temporary"
}
1 => {#3722 ▼
+"id": "1"
+"select_type": "PRIMARY"
+"table": "property_values"
+"type": "range"
+"possible_keys": "PRIMARY,property_values_property_id_index,property_values_string_index"
+"key": "property_values_string_index"
+"key_len": "767"
+"ref": null
+"rows": "2"
+"Extra": "Using index condition; Using where; Using join buffer (flat, BNL join)"
}
2 => {#3518 ▼
+"id": "1"
+"select_type": "PRIMARY"
+"table": "product_property_value"
+"type": "ref"
+"possible_keys": "product_property_value_product_id_foreign,product_property_value_property_value_id_foreign"
+"key": "product_property_value_property_value_id_foreign"
+"key_len": "8"
+"ref": "stmwmfjk_store.property_values.id"
+"rows": "10"
+"Extra": ""
}
3 => {#3084 ▼
+"id": "1"
+"select_type": "PRIMARY"
+"table": "products"
+"type": "eq_ref"
+"possible_keys": "PRIMARY"
+"key": "PRIMARY"
+"key_len": "8"
+"ref": "stmwmfjk_store.product_property_value.product_id"
+"rows": "1"
+"Extra": ""
}
4 => {#3697 ▼
+"id": "1"
+"select_type": "PRIMARY"
+"table": "product_property_value"
+"type": "ref"
+"possible_keys": "product_property_value_product_id_foreign,product_property_value_property_value_id_foreign"
+"key": "product_property_value_property_value_id_foreign"
+"key_len": "8"
+"ref": "stmwmfjk_store.property_values.id"
+"rows": "10"
+"Extra": "Using where; End temporary"
}
5 => {#3728 ▼
+"id": "1"
+"select_type": "PRIMARY"
+"table": "product_property_value"
+"type": "ref"
+"possible_keys": "product_property_value_product_id_foreign,product_property_value_property_value_id_foreign"
+"key": "product_property_value_product_id_foreign"
+"key_len": "8"
+"ref": "stmwmfjk_store.product_property_value.product_id"
+"rows": "19"
+"Extra": ""
}
6 => {#3729 ▼
+"id": "1"
+"select_type": "PRIMARY"
+"table": "property_values"
+"type": "eq_ref"
+"possible_keys": "PRIMARY,property_values_property_id_index,property_values_double_index"
+"key": "PRIMARY"
+"key_len": "8"
+"ref": "stmwmfjk_store.product_property_value.property_value_id"
+"rows": "1"
+"Extra": "Using where; FirstMatch(product_property_value)"
}
This is with 10 selected filters with different logic "or" "and" when i need or logic in use whereIn(key, [values])... query time 3.5 seconds. It is TOO LONG. Indexes I set for ids and values. Can you suggest please, where should I look for a problem?
Please or to participate in this conversation.