2

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)
  • The killer is the "dependent subquery" (correlated subquery). What ENGINE are you using? – Rick James Apr 17 '15 at 3:09
  • @RickJames I'm using MyISAM – MacMac Apr 17 '15 at 7:35
  • @RickJames Hi. Do you know if there is an alternate method instead of using a dependent subquery to speed this query up? – MacMac Apr 20 '15 at 7:51
  • Could you upload a mysqldump to dropbox or similar? You can protect anything sensitive with md5 or similar. – Vérace Apr 20 '15 at 18:38
  • The statement has some JOINs that I can't quite decipher. Please do EXPLAIN EXTENDED SELECT ...;, then SHOW WARNINGS;. That should provide a rewrite of the query that shows how it parsed the JOINs. – Rick James Apr 20 '15 at 22:44
2

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.

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
INNER JOIN (
    SELECT MAX(status_id) AS latest_status
    FROM order_item_status
    GROUP BY item_id
    ) ois2 ON ois.status_id = ois2.latest_status
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN status_types st ON st.status_id = ois.STATUS
    AND 1 = (
        CASE 
            WHEN st.item_group IS NULL
                THEN 1
            WHEN st.item_group = p.item_group
                THEN 1
            ELSE 0
            END
        )
    AND 1 = (
        CASE 
            WHEN st.gift_message_only = 0
                THEN 1
            WHEN st.gift_message_only = 1
                AND oi.gift_message_engraved = 1
                THEN 1
            ELSE 0
            END
        )
WHERE 
    EXISTS (SELECT 1 FROM orders o WHERE o.order_id = oi.order_id AND o.is_deleted = 0 AND o.needs_review = 0 )
    AND oi.is_deleted = 0
GROUP BY st.status_id
|improve this answer|||||
  • Thanks for your effort Nicholai, but unfortunately this doesn't seem to count correctly. I've uploaded a SQL file that contains the relevant files if you would like to work with them. mega.co.nz/… – MacMac Apr 21 '15 at 8:22
  • There must be something I misunderstood between ois.status and ois.status_id. However, you can still try the conditional joins for status_type, to hopefully get a bit of performance boost in that section. I'll post another answer with something else to try as well. – Nicholai Apr 21 '15 at 14:30
  • I've edited the answer to improve that join to order_item_status. We still hit the table twice, but I think this method will be faster. – Nicholai Apr 21 '15 at 14:56
  • Hi Nicholai, thanks for that. I've done the exact same as your updated edit when I figured that you used the status_id/status incorrectly. Thanks for your help as this has sped up the query! – MacMac Apr 21 '15 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.