Slow MYSQL query after using inner join and sub query

Question

I have spent hours trying to get my query to run faster so far it works on my database.
however it takes 43 seconds to return my result. basically two tables are joined and I need to only return the latest order_history_id for each order_id with an order_status of 12.

I have tried using table shortcuts ie T1 T2 etc but to keep it simple my sql query has the relevant tables names below any help greatly appreciated

SELECT oc_order.order_id, oc_order.firstname, oc_order.lastname
   FROM oc_order 
      INNER JOIN oc_order_history ON oc_order.order_id = oc_order_history.order_id
         AND oc_order_history.comment NOT LIKE '' 
         AND oc_order_history.order_status_id LIKE '12'   
         AND order_history_id = (SELECT max(order_history_id) 
                                  FROM   oc_order_history i 
                                  WHERE  i.order_id = oc_order.order_id)

Please run SHOW CREATE TABLE <tablename> on each table in the query and post to the question so we can see your schema and indexes. Also run explain <your query> and post the results. — Ray
– Ray, Commented Mar 8, 2014 at 17:12
Hi Ray, its not allowing me to post the entire schema I have however found it on a picture link I have tried to run explain <my query> but it gives error code 1064 — BenXvisual
– BenXvisual, Commented Mar 8, 2014 at 17:38

shadyyx · Accepted Answer · 2014-03-10 12:26:59Z

0

INNER JOIN is little bit more resources eating as it adds another filtering condition, i.e. it is really a LEFT JOIN + WHERE {joining column} IS NOT NULL. Having indexes on columns taking part in joins and/or where clauses (especially string values that are searched with LIKE) will help to minimise the resources cost.

In Your query You are using LIKE in WHERE clause but completely wrong thus I can say You are misusing it. LIKE is used when You need to search for strings but You only know part of the string, e.g.

name LIKE 'Pet%'

will match all the rows with names like Pete, Peter, Petra, Petronela, Petriarca, Peter Pan, etc... If You want to search for exact value, compare with comparator signs =, <>, <=, >=, e.g.

name = 'Peter'

Now consider also this query:

SELECT o.order_id, o.firstname, o.lastname
FROM oc_order o
LEFT JOIN oc_order_history oh USING(order_id)
WHERE oh.order_status_id = 12   
    AND oh.order_history_id IN (
        SELECT MAX(order_history_id) 
        FROM oc_order_history
        GROUP BY order_history_id
    )

answered Mar 10, 2014 at 12:26

shadyyx

16.1k7 gold badges62 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BenXvisual Over a year ago

Hi Shadyyx thank you for your help, after changing the bottom line to GROUP BY order_id to filter out only the most recent items the query runs in just 8 seconds on my production database, hopefully after indexing this will be reduced down again. also thank you for explaining the uses.

Community · Accepted Answer · 2017-05-23 10:33:26Z

0

Do not use LIKE if you do not want to search a text inside of a field. Also the do not put the WHERE conditions into the JOIN conditions. In addition, check that you have SQL Indexes in the fields that you are joining and filtering on.

Update: after taking a look at your tables, I realised that oc_order_history.comment is a Text column, so search on this column is going to be very slow. If you try to remove the condition (like I did) I am sure your query it will be much faster. If you have to query for the comment column, try to change it to a VARCHAR or put and index.

Also you should have indexes at least in oc_order_history(order_status_id) and in oc_order_history(order_id)

SELECT oc_order.order_id, oc_order.firstname, oc_order.lastname
FROM oc_order 
INNER JOIN oc_order_history ON oc_order.order_id = oc_order_history.order_id
WHERE oc_order_history.order_status_id = 12   
  AND order_history_id = (SELECT MAX(order_history_id) 
                          FROM oc_order_history i 
                          WHERE i.order_id = oc_order.order_id)

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Mar 8, 2014 at 17:16

Rafa Paez

4,87023 silver badges35 bronze badges

2 Comments

BenXvisual Over a year ago

hi @Rafa thank you for your help, I have changed the query as you mentioned it definitely makes sense I am unfamiliar with <b>SQL Indexes</b> would adding an index to my frequently searched tables help a great deal? also worth noting the query now takes 84.5 seconds to complete that has to be a new record, is it possible to sort data before selecting it? Thank you

BenXvisual Over a year ago

Hi Rafa, thank you for all your help, your query works beautifully on my test database, on the production one it is a little slower as soon as I can figure out the proper usage I will add the indexes as you mentioned. Thanks for all your help

Collectives™ on Stack Overflow

Slow MYSQL query after using inner join and sub query

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related