mysql - InnoDB and tmp_table issue -

i have set of 3 innodb tables in mysql server on simple select.

select s.type, s.price,l.ratio, o.type,  structures s, orders o, legs l   s.type in ('type1','type2',...)   , o.id >= s.id* 10   , o.id<= s.id * 10 + s.ordernumber -1   , l.id >= s.id * 10   , l.id <= s.id * 10 + s.legnumber -1    order s.type, s.furthestexpiration, s.nearestexpiration 

after number of rows added (around 1,800 structures, 3,000 legs , orders), cannot execute request , see in mysql workbench request's state

copying tmp table.  

the request never finishes.

some remarks:

1. if same request in workbench, succeeds.
2. if remove order clause, succeeds.
3. if switch engine innodb myisam, works.

i don't care using innodb or myisam, not sure that's incompatibility innodb, suspect there issues in db parameters/design.

thanks clue!

i'm not sure agree "simple select". want here? because if see correctly, huge (implicit) join. joining saying this: from structures s, orders o, legs l, there no s.id = o.id kind-of thing, comparison. how big resultset be? there might not easy index either, you'll have check explain that.

anyway, see no reason should never finish, although because of query not being efficient might long time before copying tmp table. state finishes. (obviously depending on fact if query inefficient :) ) maybe hitting limit of sort (caching, memory etc) on amount of rows, , starts become slow, , looks it's not finishing.

my advice make explicit joins ( table1 t1 join table2 t2 on t1.id = t2.id) if possible, or @ least check how 'heavy' query is.