MySQL's optimizer is not great, so although it should get the same execution plan, it may well be that it favors the FROM clause. What you can try About the query itself is to move the subqueries to the FROM clause. create index idx1 on cats(id, name) Ĭreate index idx2 on suppliers(id, company) Ĭreate index idx3 on stocks(product_id, total_quantity) Ĭreate index idx4 on sale_items(product_id, quantity, sale_id) indexes that contain all columns you need from a table, so the data can be taken from the index directly. What you can do, though, is provide covering indexes, i.e. And at a first glance there is not much we seem to be able to do here. Your explain plan shows there are indexes on the IDs, which is good.
You select all data and aggregate some of it. I am expecting that the query takes less that 3s at most, but I can't seem to figure out the best way to optimize this query. | 3 | DEPENDENT SUBQUERY | stocks | ref | IDX_stocks_product_id | IDX_stocks_product_id | 5 | p.id | 1 | Using where | | 4 | DEPENDENT SUBQUERY | sale_items | ref | sales_items_product_id | sales_items_product_id | 5 | p.id | 33 | Using where | | 6 | DEPENDENT SUBQUERY | refunds | index_subquery | IDX_refunds_sale_id | IDX_refunds_sale_id | 5 | func | 1 | Using index Using where | | 5 | DEPENDENT SUBQUERY | sale_items | ref | sales_items_product_id | sales_items_product_id | 5 | p.id | 33 | Using where | | 2 | DERIVED | s | eq_ref | PRIMARY | PRIMARY | 4 | p.supplier_id | 1 | | | 2 | DERIVED | c | eq_ref | PRIMARY | PRIMARY | 4 | p.cat_id | 1 | | | 2 | DERIVED | p | ALL | NULL | NULL | NULL | NULL | 20934 | | | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 20981 | | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | WHERE ((`sale_items`.`product_id` = `p`.`id`) AND `sale_items`.`Sale_ID` IN (SELECT Initially some of the sale_items and stock tables didn't have more that the ID keys, so I added some more: SELECT
The query contains multiple subquery which is taking over 120s to execute. I have a select query, that selects over 50k records from MySQL 5.5 database at once, and this amount is expected to grow.
0 kommentar(er)
