mysql - The query takes a long time -

am doing sql code right? query works, takes long, 161 seconds (with limit 2 set). there way optimize that?

 select p.itemid `id`,     p.title `name`,     pb.data_txt `birthdate`,     pc.data_txt `growth`,     pd.data_txt `eyes`,     pe.data_txt `desc`,     pf.data_txt `weight`,     pg.data_txt `sex`,     ph.data_txt `hair`,     pi.data_txt `dimensions`,     pj.data_txt `lang`,     pk.data_txt `school`,     pl.data_txt `know`,     p.image `image`    `jos_sobi2_item` p      inner join `jos_sobi2_fields_data` pb on pb.itemid  = p.itemid     inner join `jos_sobi2_fields_data` pc on pc.itemid  = p.itemid     inner join `jos_sobi2_fields_data` pd on pd.itemid  = p.itemid     inner join `jos_sobi2_fields_data` pe on pe.itemid  = p.itemid     inner join `jos_sobi2_fields_data` pf on pf.itemid  = p.itemid     inner join `jos_sobi2_fields_data` pg on pg.itemid  = p.itemid     inner join `jos_sobi2_fields_data` ph on ph.itemid  = p.itemid     inner join `jos_sobi2_fields_data` pi on pi.itemid  = p.itemid     inner join `jos_sobi2_fields_data` pj on pj.itemid  = p.itemid     inner join `jos_sobi2_fields_data` pk on pk.itemid  = p.itemid     inner join `jos_sobi2_fields_data` pl on pl.itemid  = p.itemid        pb.fieldid = 16 ,       pc.fieldid = 17 ,       pd.fieldid = 21 ,       pe.fieldid = 13 ,       pf.fieldid = 18 ,       pg.fieldid = 19 ,       ph.fieldid = 20 ,       pi.fieldid = 22 ,       pj.fieldid = 25 ,        pk.fieldid = 23 ,       pl.fieldid = 24  limit 2 

i think part of inner join bad.

try using conditional aggregation instead:

select p.itemid `id`, p.title `name`,        max(case when fd.fieldid = 16 data_txt end) birthdate,        max(case when fd.fieldid = 17 data_txt end) growth,        . . .  `jos_sobi2_item` p inner join       `jos_sobi2_fields_data` fd       on fd.itemid = p.itemid group p.itemid, p.title; 

you can add many fields like, , adding more fields have little impact on performance.

if want faster, can limit on first table:

select p.itemid `id`, p.title `name`,        max(case when fd.fieldid = 16 data_txt end) birthdate,        max(case when fd.fieldid = 17 data_txt end) growth,        . . .  (select p.*        `jos_sobi2_item` p        limit 2       ) p inner join       `jos_sobi2_fields_data` fd       on fd.itemid = p.itemid group p.itemid, p.title;