How do you optimize a MySQL query that joins on itself and does a "custom" group by? -

i have following query starting become slow size of db table increases:

select       t.*,      e.translatedvalue englishvalue (     select distinct propertykey      translations ) grouper join translations t      on t.translationid = (         select translationid          translations gt         gt.propertykey = grouper.propertykey              , gt.locale = 'es'              , gt.priority = 3         order gt.modifieddate desc          limit 1     ) inner join translations e      on t.englishtranslationid = e.translationid  order t.reviewervalidated, propertykey 

first, selecting translations, joined me corresponding english value also.

then, want limit results 1 per propertykey. group except need pick specific record 1 returned (instead of way group gives me first 1 finds). why have inner query returns 1 translationid.

when run explain following info:

is there way can return same set of results without having have mysql use slower derived table? thanks!

update: created sql fiddle schema , sample data. can run query see results gives. need able same results, in faster way. http://sqlfiddle.com/#!2/44eb0/3/0

i think want recent translatedvalue given local , priority matches propertykey in record.

if so, following want, using single correlated subquery:

 select t.*,         (select t2.translatedvalue          translations t2          t.propertykey = t2.propertykey ,                t2.locale = 'es' ,                t2.priority = 3          order t.modifieddate desc          limit 1         ) englishvalue  translations t  having englishvalue not null  order t.reviewervalidated, propertykey; 

(the having clause eliminates records no translation.)

if so, index on translations(propertykey, locale, priority, modifieddate) should speed query.