performance - MySQL nested query speed -

i'm coming postgres background , trying convert application mysql. have query fast on postgres , slow on mysql. after doing analysis, have determined 1 cause of drastic speed difference nested queries. following pseudo query takes 170 ms on postgres , 5.5 seconds on mysql.

select * (   select id inner join b ) first limit 10 

on both mysql , postgres speed same following query (less 10 ms)

select id inner join b limit 10 

i have exact same tables, indices, , data on both databases, have no idea why slow.

any insight appreciated.

thanks

edit

here 1 specific example of why need this. need sum of max. in order need sub select shown in query below.

select sum(a)   (     select table2.b, max(table1.a)     table1     inner join table2 on table2.abc_id = table1.abc_id       , table1.read_datetime >= table2.issuance_datetime       , table1.read_datetime < coalesce(table2.unassignment_datetime, date('9999-01-01'))     table1.read_datetime between '2012-01-01 10:30:01' , '2013-07-18 03:03:42' , table2.c = 0     group table2.id, b ) first group b limit 10 

again query takes 14 seconds on mysql , 238 ms on postgres. here output explain on mysql:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,extra 1,primary,<derived2>,all,\n,\n,\n,\n,25584,using temporary; using filesort 2,derived,table2,index,primary,index_table2_on_b,index_table2_on_d,index_table2_on_issuance_datetime,index_table2_on_unassignment_datetime,index_table2_on_e,primary,4,\n,25584,using 2,derived,tz,ref,index_table1_on_d,index_table1_on_read_datetime,index_table1_on_d_and_read_datetime,index_table1_on_4,4,db.table2.dosimeter_id,1,using 

jon, answering comment, here example:

drop table if exists temp_preliminary_table; create temporary table temp_preliminary_table     select table2.b, max(table1.a)     table1     inner join table2 on table2.abc_id = table1.abc_id       , table1.read_datetime >= table2.issuance_datetime       , table1.read_datetime < coalesce(table2.unassignment_datetime, date('9999-01-01'))     table1.read_datetime between '2012-01-01 10:30:01' , '2013-07-18 03:03:42' , table2.c = 0     group table2.id, b; -- suggest add indexes temp table alter table temp_preliminary_table     add index idx_b(b); -- add many indexes need -- perform query on temp_table select sum(a) temp_preliminary_table group b limit 10; 

this example, splitting query in 3 steps.

you need remember temp tables in mysql visible connection created them, other connection won't see temp tables created connection (for better or worse).

this "divide-and-conquer" approach has saved me many headaches. hope helps you.