I have an Intranet system developed that takes in orders and every order item has their own status, i.e. "Printed", "Labelled", "Filed", etc. This query will need to count how many items there are in each group. However, it seems to run from 5 to 20 seconds just to get the numbers returned.
The slow query:
SELECT COUNT(1) AS `total_count`,
`st`.`label`,
`st`.`status_id`
FROM
(
`order_items` `oi`
INNER JOIN `order_item_status` `ois`
ON `ois`.`item_id` = `oi`.`item_id`
AND `ois`.`status` = (
SELECT `ois2`.`status`
FROM `order_item_status` `ois2`
WHERE `ois2`.`item_id` = `ois`.`item_id`
ORDER BY `ois2`.`status_id` DESC
LIMIT 1
)
INNER JOIN `products` `p` ON `oi`.`product_id` = `p`.`product_id`
INNER JOIN `status_types` `st`
ON `st`.`status_id` = `ois`.`status`
AND ( `st`.`item_group` IS NULL
OR `st`.`item_group` = `p`.`item_group` )
AND ( `st`.`gift_message_only` = 0
OR (`st`.`gift_message_only` = 1
AND `oi`.`gift_message_engraved` = 1) )
INNER JOIN `orders` `o`
ON `o`.`order_id` = `oi`.`order_id` AND `o`.`is_deleted` = 0
)
WHERE `o`.`is_deleted` = 0
AND `oi`.`is_deleted` = 0
AND `o`.`needs_review` = 0
GROUP BY `st`.`status_id`
ORDER BY NULL
Result:
+-------------+--------------+-----------+
| total_count | label | status_id |
+-------------+--------------+-----------+
| 890 | Complete | 18 |
| 6 | Dispatched | 3 |
| 8 | CNC file | 12 |
| 11 | Assemble | 7 |
| 5 | Print | 4 |
| 2 | 3rd Oil | 17 |
| 3 | Label | 5 |
| 1 | 3rd Oil | 9 |
| 12 | Hand engrave | 6 |
| 15 | 1st Oil | 15 |
| 3 | 2nd Oil | 8 |
| 3 | Sand | 14 |
+-------------+--------------+-----------+
12 rows in set (8.27 sec)
The EXPLAIN EXTENDED:
+----+--------------------+-------+--------+-------------------------------------+-----------+---------+-----------------------+------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+--------+-------------------------------------+-----------+---------+-----------------------+------+----------+------------------------------+
| 1 | PRIMARY | o | ALL | PRIMARY | NULL | NULL | NULL | 990 | 100.00 | Using where; Using temporary |
| 1 | PRIMARY | oi | ref | PRIMARY,order_id,product_id,item_id | order_id | 4 | oakrope.o.order_id | 1 | 100.00 | Using where |
| 1 | PRIMARY | p | eq_ref | PRIMARY,item_group,product_id | PRIMARY | 4 | oakrope.oi.product_id | 1 | 100.00 | |
| 1 | PRIMARY | ois | ref | item_id,status,item_id_2 | item_id_2 | 4 | oakrope.oi.item_id | 4 | 100.00 | Using where; Using index |
| 1 | PRIMARY | st | eq_ref | PRIMARY,status_id,status_id_2 | PRIMARY | 1 | func | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | ois2 | index | item_id,item_id_2 | PRIMARY | 4 | NULL | 1 | 400.00 | Using where |
+----+--------------------+-------+--------+-------------------------------------+-----------+---------+-----------------------+------+----------+------------------------------+
I'm not sure what to look out for now. I've tried adding indexes to the possible_keys together, but MySQL doesn't seem to be interested in using the key.
Am I overlooking this explained query, or honestly just cannot be improved for what I need?
The MySQL version is 5.1.73, the engine is MyISAM.
P.S – don't worry about the 'duplicate' 2nd/3rd oil, they are not duplicates.
EDIT: Output of SHOW WARNINGS:
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'ordersys.ois.item_id' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | select count(1) AS `total_count`,`ordersys`.`st`.`label` AS `label`,`ordersys`.`st`.`status_id` AS `status_id` from `ordersys`.`order_items` `oi` join `ordersys`.`order_item_status` `ois` join `ordersys`.`products` `p` join `ordersys`.`status_types` `st` join `ordersys`.`orders` `o` where ((`ordersys`.`ois`.`item_id` = `ordersys`.`oi`.`item_id`) and (`ordersys`.`p`.`product_id` = `ordersys`.`oi`.`product_id`) and (`ordersys`.`st`.`status_id` = `ordersys`.`ois`.`status`) and (`ordersys`.`oi`.`order_id` = `ordersys`.`o`.`order_id`) and (`ordersys`.`o`.`needs_review` = 0) and (`ordersys`.`oi`.`is_deleted` = 0) and (`ordersys`.`o`.`is_deleted` = 0) and (isnull(`ordersys`.`st`.`item_group`) or (`ordersys`.`st`.`item_group` = `ordersys`.`p`.`item_group`)) and ((`ordersys`.`st`.`gift_message_only` = 0) or ((`ordersys`.`oi`.`gift_message_engraved` = 1) and (`ordersys`.`st`.`gift_message_only` = 1))) and (`ordersys`.`ois`.`status` = (select `ordersys`.`ois2`.`status` from `ordersys`.`order_item_status` `ois2` where (`ordersys`.`ois2`.`item_id` = `ordersys`.`ois`.`item_id`) order by `ordersys`.`ois2`.`status_id` desc limit 1))) group by `ordersys`.`st`.`status_id` order by NULL |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Best Answer
Here's something to test out. You can't force the order of execution in an OR, but you can in a CASE, so you can short-circuit extra processing if you put the most common scenario at the top of each CASE.
EDIT: Since you aren't using any fields from orders (just testing that the order isn't deleted or in need of review), I moved that part to an EXISTS in the WHERE clause. When you don't need to display any fields of a table, this technique usually speeds up a query.