|
FYI, I've just hit a similar case in production where I had to kill a query after 1580s vs 0.053s with semijoin=off
So it's a really bad regression in 10.3.
Query:
SELECT
|
SQL_CALC_FOUND_ROWS
|
si.id_product, SUM(si.weight) position, GROUP_CONCAT(sw.word SEPARATOR ' ') as terms, IFNULL(stock.quantity, 0) as quantity
|
FROM ps_search_word sw
|
LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
|
LEFT JOIN ps_product p ON p.id_product=si.id_product
|
LEFT JOIN ps_product_shop product_shop ON (product_shop.id_product = si.id_product AND product_shop.id_shop = 1)
|
LEFT JOIN ps_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = 0 AND stock.id_shop = 1 AND stock.id_shop_group = 0 )
|
LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
|
WHERE sw.id_lang = 1 AND sw.id_shop = 1 AND ((sw.id_word
|
IN(70753384,70513881,71090789,71038048,70528020,70444280,70514106,71091008,71038217,70753558,70514165,71091100,71038307)) OR (sw.word LIKE "%fase1%"))
|
AND si.id_product IN(
|
SELECT
|
DISTINCT cp.`id_product`
|
FROM `ps_category_group` cg
|
INNER JOIN `ps_category_product` cp ON cp.`id_category` = cg.`id_category`
|
INNER JOIN `ps_category` c ON cp.`id_category` = c.`id_category`
|
INNER JOIN `ps_product` p ON cp.`id_product` = p.`id_product`
|
LEFT JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
|
WHERE c.`active` = 1
|
AND product_shop.`active` = 1
|
AND product_shop.`visibility` IN ("both", "search")
|
AND product_shop.indexed = 1
|
AND cg.`id_group` IN (
|
SELECT id_group FROM ps_customer_group
|
WHERE id_customer = 51952)
|
)
|
GROUP BY si.id_product HAVING (terms LIKE "%fase%")
|
ORDER BY position desc,p.id_product;
|
ANALYZE output with semijoin:
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 2.01e6,
|
"having_condition": "terms like '%fase%'",
|
"filesort": {
|
"sort_key": "sum(si.weight) desc, p.id_product",
|
"r_loops": 1,
|
"r_total_time_ms": 0.061,
|
"r_used_priority_queue": false,
|
"r_output_rows": 151,
|
"r_buffer_size": "5Kb",
|
"temporary_table": {
|
"filesort": {
|
"sort_key": "si.id_product",
|
"r_loops": 1,
|
"r_total_time_ms": 0.0319,
|
"r_used_priority_queue": false,
|
"r_output_rows": 151,
|
"r_buffer_size": "3Kb",
|
"temporary_table": {
|
"duplicates_removal": {
|
"table": {
|
"table_name": "ps_customer_group",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "customer_login", "id_customer"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id_customer"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0248,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "cg",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "id_category", "id_group"],
|
"key": "id_group",
|
"key_length": "4",
|
"used_key_parts": ["id_group"],
|
"ref": ["outletsaludcom.ps_customer_group.id_group"],
|
"r_loops": 1,
|
"rows": 115,
|
"r_rows": 249,
|
"r_total_time_ms": 0.1637,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "c",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "activenleft", "activenright"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id_category"],
|
"ref": ["outletsaludcom.cg.id_category"],
|
"r_loops": 249,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.676,
|
"filtered": 100,
|
"r_filtered": 85.141,
|
"attached_condition": "c.active = 1"
|
},
|
"table": {
|
"table_name": "cp",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "id_product", "id_category"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id_category"],
|
"ref": ["outletsaludcom.cg.id_category"],
|
"r_loops": 212,
|
"rows": 21,
|
"r_rows": 42.354,
|
"r_total_time_ms": 3.6359,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "p",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id_product"],
|
"ref": ["outletsaludcom.cp.id_product"],
|
"r_loops": 8979,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 14.591,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "product_shop",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "indexed"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id_product", "id_shop"],
|
"ref": ["outletsaludcom.cp.id_product", "const"],
|
"r_loops": 8979,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 17.941,
|
"filtered": 100,
|
"r_filtered": 87.983,
|
"attached_condition": "product_shop.active = 1 and product_shop.indexed = 1 and product_shop.visibility in ('both','search')"
|
},
|
"table": {
|
"table_name": "si",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "id_product"],
|
"key": "id_product",
|
"key_length": "4",
|
"used_key_parts": ["id_product"],
|
"ref": ["outletsaludcom.cp.id_product"],
|
"r_loops": 7900,
|
"rows": 217,
|
"r_rows": 430.45,
|
"r_total_time_ms": 620.98,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
}
|
},
|
"table": {
|
"table_name": "p",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id_product"],
|
"ref": ["outletsaludcom.cp.id_product"],
|
"r_loops": 765047,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 2.6409,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "stock",
|
"access_type": "ref",
|
"possible_keys": [
|
"product_sqlstock",
|
"id_shop",
|
"id_shop_group",
|
"id_product",
|
"id_product_attribute"
|
],
|
"key": "id_product",
|
"key_length": "4",
|
"used_key_parts": ["id_product"],
|
"ref": ["outletsaludcom.p.id_product"],
|
"r_loops": 765047,
|
"rows": 1,
|
"r_rows": 1.0408,
|
"r_total_time_ms": 1123.1,
|
"filtered": 100,
|
"r_filtered": 96.077,
|
"attached_condition": "trigcond(stock.id_product_attribute = 0 and stock.id_shop = 1 and stock.id_shop_group = 0 and trigcond(p.id_product is not null))"
|
},
|
"table": {
|
"table_name": "sw",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "id_lang"],
|
"key": "id_lang",
|
"key_length": "8",
|
"used_key_parts": ["id_lang", "id_shop"],
|
"ref": ["const", "const"],
|
"r_loops": 765047,
|
"rows": 11006,
|
"r_rows": 10758,
|
"r_total_time_ms": 1.59e6,
|
"filtered": 100,
|
"r_filtered": 1.8e-6,
|
"attached_condition": "sw.id_word = si.id_word and (sw.id_word in (70753384,70513881,71090789,71038048,70528020,70444280,70514106,71091008,71038217,70753558,70514165,71091100,71038307) or sw.word like '%fase1%')",
|
"using_index": true
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
ANALYSE Without semijoin:
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 41.687,
|
"having_condition": "terms like '%fase%'",
|
"filesort": {
|
"sort_key": "sum(si.weight) desc, p.id_product",
|
"r_loops": 1,
|
"r_total_time_ms": 0.0887,
|
"r_used_priority_queue": false,
|
"r_output_rows": 151,
|
"r_buffer_size": "5Kb",
|
"temporary_table": {
|
"filesort": {
|
"sort_key": "si.id_product",
|
"r_loops": 1,
|
"r_total_time_ms": 0.0317,
|
"r_used_priority_queue": false,
|
"r_output_rows": 151,
|
"r_buffer_size": "3Kb",
|
"temporary_table": {
|
"table": {
|
"table_name": "sw",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "id_lang"],
|
"key": "id_lang",
|
"key_length": "8",
|
"used_key_parts": ["id_lang", "id_shop"],
|
"ref": ["const", "const"],
|
"r_loops": 1,
|
"rows": 11006,
|
"r_rows": 10758,
|
"r_total_time_ms": 4.77,
|
"filtered": 100,
|
"r_filtered": 0.0372,
|
"attached_condition": "sw.id_word in (70753384,70513881,71090789,71038048,70528020,70444280,70514106,71091008,71038217,70753558,70514165,71091100,71038307) or sw.word like '%fase1%'",
|
"using_index": true
|
},
|
"table": {
|
"table_name": "si",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id_word"],
|
"ref": ["outletsaludcom.sw.id_word"],
|
"r_loops": 4,
|
"rows": 6,
|
"r_rows": 39,
|
"r_total_time_ms": 0.1247,
|
"filtered": 100,
|
"r_filtered": 97.436,
|
"attached_condition": "trigcond(<in_optimizer>(si.id_product,si.id_product in (subquery#2)))"
|
},
|
"table": {
|
"table_name": "p",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id_product"],
|
"ref": ["outletsaludcom.si.id_product"],
|
"r_loops": 151,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.2148,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "trigcond(trigcond(si.id_product is not null))"
|
},
|
"table": {
|
"table_name": "stock",
|
"access_type": "eq_ref",
|
"possible_keys": [
|
"product_sqlstock",
|
"id_shop",
|
"id_shop_group",
|
"id_product",
|
"id_product_attribute"
|
],
|
"key": "product_sqlstock",
|
"key_length": "16",
|
"used_key_parts": [
|
"id_product",
|
"id_product_attribute",
|
"id_shop",
|
"id_shop_group"
|
],
|
"ref": ["outletsaludcom.p.id_product", "const", "const", "const"],
|
"r_loops": 151,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.3796,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "trigcond(trigcond(p.id_product is not null))"
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 1,
|
"r_total_time_ms": 30.137,
|
"table": {
|
"table_name": "c",
|
"access_type": "ref",
|
"possible_keys": [
|
"PRIMARY",
|
"category_parent",
|
"nleftrightactive",
|
"level_depth",
|
"nright",
|
"activenleft",
|
"activenright"
|
],
|
"key": "activenleft",
|
"key_length": "1",
|
"used_key_parts": ["active"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 213,
|
"r_rows": 213,
|
"r_total_time_ms": 0.0726,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "cg",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "id_category", "id_group"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id_category"],
|
"ref": ["outletsaludcom.c.id_category"],
|
"r_loops": 213,
|
"rows": 1,
|
"r_rows": 3.7793,
|
"r_total_time_ms": 0.551,
|
"filtered": 100,
|
"r_filtered": 26.335,
|
"attached_condition": "<in_optimizer>(cg.id_group,cg.id_group in (subquery#3))",
|
"using_index": true
|
},
|
"table": {
|
"table_name": "cp",
|
"access_type": "ref",
|
"possible_keys": ["PRIMARY", "id_product", "id_category"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id_category"],
|
"ref": ["outletsaludcom.c.id_category"],
|
"r_loops": 212,
|
"rows": 21,
|
"r_rows": 42.354,
|
"r_total_time_ms": 3.0359,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "product_shop",
|
"access_type": "eq_ref",
|
"possible_keys": [
|
"PRIMARY",
|
"id_category_default",
|
"date_add",
|
"indexed"
|
],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["id_product", "id_shop"],
|
"ref": ["outletsaludcom.cp.id_product", "const"],
|
"r_loops": 8979,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 8.312,
|
"filtered": 100,
|
"r_filtered": 87.983,
|
"attached_condition": "product_shop.active = 1 and product_shop.indexed = 1 and product_shop.visibility in ('both','search')"
|
},
|
"table": {
|
"table_name": "p",
|
"access_type": "eq_ref",
|
"possible_keys": [
|
"PRIMARY",
|
"product_supplier",
|
"product_manufacturer",
|
"id_category_default",
|
"indexed",
|
"date_add"
|
],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id_product"],
|
"ref": ["outletsaludcom.cp.id_product"],
|
"r_loops": 7900,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 10.726,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 3,
|
"r_loops": 1,
|
"r_total_time_ms": 0.0188,
|
"table": {
|
"table_name": "ps_customer_group",
|
"access_type": "ref",
|
"possible_keys": [
|
"PRIMARY",
|
"customer_login",
|
"id_customer"
|
],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["id_customer"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0085,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
}
|
}
|
}
|
]
|
}
|
}
|
]
|
}
|
}
|
}
|
}
|
}
|
}
|
I'll post the ANALYZE for the first query once it finishes its execution, can take a while
|