buenos días,
como podría optimizar esta consulta, que tarda en ejecutarse directamente a la db mysql 60 segundos:
SELECT vtiger_crmentity.crmid, vtiger_crmentity.smownerid, vtiger_leaddetails.firstname, vtiger_leaddetails.lastname, vtiger_leaddetails.company, vtiger_leadaddress.phone, vtiger_leadsubdetails.website, vtiger_leaddetails.email, vtiger_leadscf . *
FROM vtiger_leaddetails
INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_leaddetails.leadid
INNER JOIN vtiger_leadsubdetails ON vtiger_leadsubdetails.leadsubscriptionid = vtiger_leaddetails.leadid
INNER JOIN vtiger_leadaddress ON vtiger_leadaddress.leadaddressid = vtiger_leadsubdetails.leadsubscriptionid
INNER JOIN vtiger_leadscf ON vtiger_leaddetails.leadid = vtiger_leadscf.leadid
LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid
LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
WHERE vtiger_crmentity.deleted =0
AND vtiger_leaddetails.converted =0
esta es la salida cuando aplico EXPLAIN
Filas: 7
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE vtiger_leaddetails ref PRIMARY,leaddetails_converted_leadstatus_idx leaddetails_converted_leadstatus_idx 5 const 653072 Using where
1 SIMPLE vtiger_leadaddress eq_ref PRIMARY PRIMARY 4 prevtiger.vtiger_leaddetails.leadid 1
1 SIMPLE vtiger_crmentity eq_ref PRIMARY,crmentity_deleted_idx PRIMARY 4 prevtiger.vtiger_leaddetails.leadid 1 Using where
1 SIMPLE vtiger_groups eq_ref PRIMARY PRIMARY 4 prevtiger.vtiger_crmentity.smownerid 1 Using index
1 SIMPLE vtiger_users eq_ref PRIMARY PRIMARY 4 prevtiger.vtiger_crmentity.smownerid 1 Using index
1 SIMPLE vtiger_leadscf eq_ref PRIMARY PRIMARY 4 prevtiger.vtiger_crmentity.crmid 1 Using where
1 SIMPLE vtiger_leadsubdetails eq_ref PRIMARY PRIMARY 4 prevtiger.vtiger_crmentity.crmid 1 Using where
como puedo optimizarla?
un saludo,
josé carlos.
