InnoDB and tmp_table issue

I have a set of three InnoDB tables in a MySQL server on which I do a simple SELECT.

SELECT s.type, s.Price,l.ratio, o.type, 
FROM Structures s, Orders o, Legs l 

WHERE s.type in ('type1','type2',...)  
AND o.ID >= s.ID* 10  
AND o.ID<= s.ID * 10 + s.orderNumber -1  
AND l.ID >= s开发者_开发百科.ID * 10  
AND l.ID <= s.ID * 10 + s.legNumber -1  

ORDER BY s.type, s.FurthestExpiration, s.NearestExpiration

After a certain number of rows added (around 1,800 for Structures, 3,000 for Legs and Orders), I cannot execute this request and I see in MySQL workbench that the request's state is

Copying to the tmp table. 

The request never finishes.

Some remarks:

1. If I do the same request in the Workbench, it succeeds.
2. If I remove the ORDER BY clause, it succeeds.
3. If I switch the engine from InnoDB to MyISAM, it works.

I don't really care about using InnoDB or MyISAM, but I am not sure that's an incompatibility with InnoDB, I suspect there are some issues in my DB parameters/design.

Thanks for any clue!

I'm not sure I agree that this is a "simple select". What do you want to do here? Because if I see this correctly, this will be a HUGE (implicit) join. You are joining by saying this: FROM Structures s, Orders o, Legs l, but there is no s.id = o.id kind-of thing, only a comparison. How big will your resultset be? There might not be an easy index either, but you'll have to check EXPLAIN for that.

Anyway, I see no reason it should never finish, although because of the query itself not being very efficient it might be a long time before this Copying to the tmp table. state finishes. (obviously depending on the fact if the query is inefficient :) ) Maybe you are hitting a limit of some sort (caching, memory etc) on that amount of rows, and so it starts to become really slow, and it looks like it's not finishing.

My advice would be to make some explicit joins ( table1 t1 JOIN table2 t2 ON t1.id = t2.id) if possible, or at least check how 'heavy' the query is.