query optimization - MySQL Join is taking too long -

i need optimizing mysql query or table

when run query return in .01s 650 records:

  select mm, name, display, year   tbl d   active = 1 , tbl2_id = 'val' , lvl_id = 9   order mm; 

when run query return in on 15s same records:

  select d.mm, d.name, d.display, d.year, a.year year2   tbl d left join tbl on d.mm = a.mm , a.tbl2_id = 'val2'   d.active = 1 , d.tbl2_id = 'val' , d.lvl_id = 9   order d.mm; 

when run take on 15s:

  select mm, name, display, year,        (select a.year tbl a.mm = mm , a.tbl2_id = 'val2') year2   tbl    active = 1 , tbl2_id = 'val' , lvl_id = 9   order mm; 

the table has multiple records mm. need records tbl2_id = 'val' , if there record mm tbl2_id = 'val2', need value of "year" val2 record. tbl has 13k records in , there no more 10 records given mm don't think think query should taking on 15s. have indexes mm, active, tbl2_id , lvl_id.

i've done similar things in mssql no delay.

you start introducing composite indexes on table on (tbl2_id, lvl_id, active) , (tbl2_id, mm). speed 3 of queries.

whenever use multiple fields in where clause, makes sense consider composite index. in case single-column indexes present, query can use 1 of indexes seek while having resort slower scan search remaining subset. difference between mysql , mssql in respect might mssql makes better guess of 3 use based on cardinality of data (the best 1 use 1 leaves smallest subset scan), although hard without examining 2 query plans in detail.