CODEWITHSUNDEEP
phpmysql
user2898068
user2898068

Reputation: 13

mysql select Where (subquery) IN

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?

Upvotes: 1

Views: 109

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

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 |

Upvotes: 1

laxertu
laxertu

Reputation: 131

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

Upvotes: 1

Related Questions