Optimise ecommerce life time metric query #18036
Description
I'm meaning this query https://github.com/matomo-org/matomo/blob/4.5.0-b2/plugins/Ecommerce/VisitorDetails.php#L154-L169 which looks like this
SELECT COALESCE(SUM(ROUND(revenue,2)), 0) as lifeTimeRevenue, COUNT(*) as lifeTimeConversions,COALESCE(SUM(ROUND(items,2)), 0) as lifeTimeEcommerceItems FROM log_visit AS log_visit LEFT JOIN log_conversion AS log_conversion ON log_visit.idvisit = log_conversion.idvisit WHERE log_visit.idsite = '2' AND log_visit.idvisitor = 'Vè�tO' AND log_conversion.idgoal = 0At first glance the query looks good as it uses indexes etc.
This is usually also the case
However, things change when one visitor has hundreds or thousands of visits. Then it looks like this:
Here is more explain format=json information:
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "365.16"
},
"nested_loop": [
{
"table": {
"table_name": "log_conversion",
"access_type": "ALL",
"possible_keys": [
"PRIMARY"
],
"rows_examined_per_scan": 738,
"rows_produced_per_join": 73,
"filtered": "10.00",
"cost_info": {
"read_cost": "261.84",
"eval_cost": "14.76",
"prefix_cost": "276.60",
"data_read_per_join": "2M"
},
"used_columns": [
"idvisit",
"idgoal",
"items",
"revenue"
],
"attached_condition": "((`test`.`log_conversion`.`idgoal` = 0) and (`test`.`log_conversion`.`idvisit` is not null))"
}
},
{
"table": {
"table_name": "log_visit",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"index_idsite_config_datetime",
"index_idsite_datetime",
"index_idsite_idvisitor"
],
"key": "PRIMARY",
"used_key_parts": [
"idvisit"
],
"key_length": "8",
"ref": [
"test.log_conversion.idvisit"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 3,
"filtered": "5.00",
"cost_info": {
"read_cost": "73.80",
"eval_cost": "0.74",
"prefix_cost": "365.16",
"data_read_per_join": "113K"
},
"used_columns": [
"idvisit",
"idsite",
"idvisitor"
],
"attached_condition": "((`test`.`log_visit`.`idvisitor` = (@`id`)) and (`test`.`log_visit`.`idsite` = '2'))"
}
}
]
}
}FYI Overall, in that specific case, there is actually no entry that matches idsite = 2 and idgoal = 0 in log_conversion.
refs #16904
Not sure if we can improve this query, or maybe not execute it in the first place. Like if we are requesting the data for only one site, we could eg check if ecommerce is enabled for that site. In this specific example where we had this problem it would have fixed it. Maybe we can also find a way though to further improve this.
For more details, or if you want me to test queries please ping me.
To summarise the todo list
- Improve query performance
- Only execute query if ecommerce feature is enabled
- We should also check if there are few other similar queries that query by
where idvisitor = ?and join other tables - Group the two queries in into one see Optimise ecommerce life time metric query #18036 (comment)
matomo/plugins/Ecommerce/VisitorDetails.php
Lines 133 to 137 in 7ff0e20