mysql select Where (subquery) IN

Question

I have a mysql query of type

select some_value FROM table WHERE (subquery) IN ( values )

which seems to be extremly slow!

I have a mysql table with orders and a second one with the corresponding processing states of them. I want now to show all orders having their last status code = 1 .

table order (id = primary key)

id | value
---+-------
1  +  10
2  +  12
3  +  14

table state (id = primary key)

id | orderid | code
---+---------+-------
1  +  1      +  1
2  +  2      +  1
3  +  2      +  2
4  +  1      +  3
5  +  3      +  1

My query is:

select order.id FROM order WHERE
( select state.code FROM state WHERE state.orderid = order.id ORDER BY state.id DESC LIMIT 0,1 )     IN ( '1' )

It takes roughly 15 seconds to process this for a single order. How to modify the mysql statement in order to speed the query procession time up?

D'Arcy Rittich · Accepted Answer · 2013-10-19 18:23:41Z

1

Update

Try this one:

select s1.orderid
from state s1
left outer join state s2 on s1.orderid = s2.orderid
    and s2.id > s1.id
where s1.code = 1
    and s2.id is null

You may need an index on state.orderid.

SQL Fiddle Example

CREATE TABLE state
    (`id` int, `orderid` int, `code` int)
;

INSERT INTO state
    (`id`, `orderid`, `code`)
VALUES
    (1, 1, 1),
    (2, 2, 1),
    (3, 2, 2),
    (4, 1, 3),
    (5, 3, 1)
;

Query:

select s1.orderid
from state s1
left outer join state s2 on s1.orderid = s2.orderid
    and s2.id > s1.id
where s1.code = 1
    and s2.id is null

Results:

| ORDERID |
|---------|
|       3 |

edited Oct 19, 2013 at 18:23

answered Oct 19, 2013 at 15:22

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

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

4 Comments

user2898068 Over a year ago

Hereby I only will get a single order, since LIMIT 0,1 acts on all orders with all states.

D'Arcy Rittich Over a year ago

Sorry, did not read the question well enough. Try my updated query.

user2898068 Over a year ago

Thank you. It works quite fast! However, as i apply an index on state.orderid the execution time "tends to be zero". Can you comment on the behavior of this index and why this speeds the query up that enormously?

D'Arcy Rittich Over a year ago

Simplistically, an index as a sorted list of that column, with a pointer back to the record in the table. E.g. you can find someone by their name quickly in the phone book because it is sorted (indexed) by lastname, firstname. Trying to find someone in the phone book by their phone number is the equivalent of not having an index. You would have to check every entry until you get a match.

zzlalani · Accepted Answer · 2013-10-19 18:02:31Z

1

in this situation I think you could simply forget of order, as all information stays in state.

SELECT x.id, x.orderid
FROM state AS x
WHERE x.code =1
AND x.id = (
SELECT max( id )
FROM a
WHERE orderid = x.orderid )

Maybe would be possible to change your CRUD by putting last state directly in order table too, this would be the better

edited Oct 19, 2013 at 18:02

zzlalani

24.6k16 gold badges48 silver badges73 bronze badges

answered Oct 19, 2013 at 17:08

laxertu

1313 bronze badges

Collectives™ on Stack Overflow

mysql select Where (subquery) IN

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related