With laravel eloquent I got sql (Mysql 8.1) of products with filers on related cities and categories.
In both cases many-to many relations :
SELECT * FROM `ts_products` WHERE `ts_products`.`title` like '%no%' AND `ts_products`.`status` = 'A' AND ts_products.published_at >= '2022-04-01' AND ts_products.published_at < '2022-04-21' AND EXISTS ( SELECT * FROM `ts_products_cities` WHERE `ts_products`.`id` = `ts_products_cities`.`product_id` AND EXISTS ( SELECT * FROM `ts_cities` WHERE `ts_products_cities`.`city_id` = `ts_cities`.`id` AND `ts_cities`.`id` in ('3255', '3739', '4192', '5434', '4678', '3297', '4529', '4036', '3051', '4502'))) AND EXISTS ( SELECT * FROM `ts_products_categories` WHERE `ts_products`.`id` = `ts_products_categories`.`product_id` AND EXISTS ( SELECT * FROM `ts_categories` WHERE `ts_products_categories`.`category_id` = `ts_categories`.`id` AND `ts_categories`.`active` = '1')) ORDER BY `regular_price` asc -- 17 rows are read
I got next explain analyze : https://prnt.sc/UpRBzl_AvkoY
What Is “temporary table” ?
What is “cost”? Some internal mysql value ? Can I have any use of it?
What is “Remove duplicate ts_products rows ” ?
What is flow is this request ? I understand some parts of it, but as it has subqueries I have difficulties in understanding it as
a whole. How correctly to read.
Why “actual time” has range values?
How explain looks : https://prnt.sc/PjTaQkyE601a
As in all cases keys are used it seemed to me good for performance, but what is type=“range” in first row?
Is there is a way to make performance of this request better ?