Problem with mysql is that after joining tables together, there will be no indexing,
making query extremely slow.
First, makesure all inner join uses on query to limit the records instead of where condition.
This way, the record is filtered based on table indexes (make sure you index'em first) in the joining process.
2nd, to only populate a field by first row which matches the criteria, do a left join
select x.field1, x.field2 from table1 x inner join table2 x2 on x.field3=x2.field2
left join table2 x3 on x2.field1=x3.field1 and x3.fields=[some condition to filter to first row]
group by ...