Optimizing query with multiple joins

[EM Ayllon]

This query:

mysql> explain select id,nombre,fecha,descripcion,cv,experiencia,password,cookie,visitas,email,directorio,cuenta,poremail,url,lasttime, edad,doc
,town,city_id,DD_LAT,DD_LONG,job_title,domain,email_sabado,password,email,sw.place as provincia, group_concat(distinct sw2.place) as location_wa
nted, group_concat(distinct ss.sector) as sector, group_concat(distinct ce.employment) as employment from candidatos_new left join shared.worldw
ide_codes as sw on sw.code = candidatos_new.provincia left join candidatos_locations_wanted as clw on clw.candidato = id left join shared.worldw
ide_codes as sw2 on sw2.code = clw.location_wanted left join candidatos_sectores as cs on cs.candidato = id left join shared.sectors as ss on ss
.code = cs.sector left join candidatos_employment as ce on ce.candidato = id where clw.location_wanted = 102004001 GROUP BY candidatos_new.id li
mit 0,23;
+----+-------------+----------------+--------+---------------------------+-----------------+---------+------------------------------------+-----
-+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+----+-------------+----------------+--------+---------------------------+-----------------+---------+------------------------------------+-----
-+----------------------------------------------+
| 1 | SIMPLE | clw | ref | candidato,location_wanted | location_wanted | 5 | const | 510
| Using where; Using temporary; Using filesort |
| 1 | SIMPLE | sw2 | ref | code | code | 5 | const | 1
| |
| 1 | SIMPLE | candidatos_new | eq_ref | PRIMARY | PRIMARY | 4 | 102001001.clw.candidato | 1
| |
| 1 | SIMPLE | cs | ref | candidato | candidato | 5 | 102001001.candidatos_new.id | 11
| |
| 1 | SIMPLE | sw | ref | code | code | 5 | 102001001.candidatos_new.provincia | 19
| |
| 1 | SIMPLE | ss | ref | code | code | 5 | 102001001.cs.sector | 17
| |
| 1 | SIMPLE | ce | ref | candidato | candidato | 5 | 102001001.clw.candidato | 11
| |
+----+-------------+----------------+--------+---------------------------+-----------------+---------+------------------------------------+-----
-+----------------------------------------------+


All joins are performed on indexed columms. As explain shows it uses a temporary table. This query is too slow and reports an "Incorrect key file for table" occasionally on the tmp table. By running top I can see that they system runs out of RAM. The constraint on the where condition is also in an indexed column 'clw.location_wanted'

Also if the constraint is done in one of the columns on the main table -called candidatos- then the query is very fast and no temporary table is used.

Could you suggest an alternative? Is it better to run a subquery?