How can I optimize a query involving a subselect in MySQL? -

suppose have table this:

id | price | group1 1  | 6     | some_group 2  | 7     | some_group 3  | 8     | some_group 4  | 9     | some_other_group 

if want select lowest price grouped group1

i can this:

select id, min(price), group1 some_table group group1; 

the problem when have table not sorted price this:

id | price | group1 1  | 8     | some_group 2  | 7     | some_group 3  | 6     | some_group 4  | 9     | some_other_group 

then query returns result set:

id | price | group1 1  | 6     | some_group 4  | 9     | some_other_group 

the problem 1 in id column id of row price of 6 not 1 3. question how can values row contains minimum price when use group by?

i tried this:

select f.id, min(f.price), f.group1 (select * some_table order price) f group f.group1; 

but slow , if have multiple columns , aggregations may fail.

please note names above demonstration purposes. real query looks this:

select depdate, retdate, min(totalprice_eur) price     (select * flight_results      (      fromcity = 30001350      , tocity = 30001249      , website = 80102118      , roundtrip = 1      , serviceclass = 1       , depdate > date(now()))      order totalprice_eur) f ( fromcity = 30001350 , tocity = 30001249 , website = 80102118 , roundtrip = 1 , serviceclass = 1 , depdate > date(now())) group depdate,retdate 

and there concatenated primary key including website, fromcity, tocity, roundtrip, depdate, , retdate. there no other indexes.

explain says:

id  select_type table   type    possible_keys   key key_len ref rows    1   primary <derived2>  null    null    null    null    2837    using where; using temporary; using filesort 2   derived flight_results  primary null    null    null    998378  using filesort 

you can instead:

select t1.id, t1.price, t1.group1 some_table t1 inner join (   select min(price) minprice, group1    some_table    group group1 ) t2 on t1.price = t2.minprice , t1.group1 = t2.group1;