"IN" Function Crashes Server

[dai.hop]

Hi all,

I have the following query...

SELECT * FROM order_lines WHERE transaction_id IN (SELECT * FROM orders WHERE DATE(due_by) = '2008-09-25')

The subquery returns 36 rows. The order_lines table is about 10,000 rows in size.

Everytime I run this query it crashes my MySQL server! How can I fix it?

Thanks,

dai.hop

[Phill Pafford]

Hmm you sub SELECT query is returning all the data due to the *

SELECT * FROM orders WHERE DATE(due_by) = '2008-09-25'

try something like this

SELECT * FROM order_lines WHERE transaction_id IN
(SELECT one_feild FROM orders WHERE DATE(due_by) = '2008-09-25')


Also using the EXPLAIN command will give some insight on how MySQL is trying to run your query


EXPLAIN SELECT * FROM order_lines WHERE transaction_id IN
(SELECT * FROM orders WHERE DATE(due_by) = '2008-09-25')

[dai.hop]

Hi Phill,

Sorry I mis-typed that bit, the query should read:

SELECT * FROM order_lines WHERE transaction_id IN
(SELECT txn_id FROM orders WHERE DATE(due_by) = '2008-09-25')

I'll try the EXPLAIN function though, thanks for that!

[dai.hop]

Tried using EXPLAIN but it didn't give me any meaningful feedback! Query still hangs the server - any other ideas? The syntax looks bang on to me and theres no more than 10,000 rows involved!

[Phill Pafford]

Are you running the query in a script or a GUI?

Also what version of MySQL are you using?

is this your own server or a shared server?

[Phill Pafford]

You could try a join


SELECT t1.feild, t1.feild, t1.etc /* These would be the feilds needed to be returned */
FROM order_lines AS t1
LEFT JOIN orders AS t2 ON (t1.transaction_id = t2.txn_id)
WHERE DATE(t2.due_by) = '2008-09-25'

[chazzy]

Try this maybe?


SELECT ol.* FROM
orders o
inner join order_lines ol on o.transaction_id = ol.transaction_id
where DATE(o.due_by) = '2008-09-25';


This should run a bit faster assuming that it's 1 : n :: orders : order_lines