Optimización de query... Tengo una aplicación con un query que me parece no es muy optimo además de sólo traer un registro cuando se encuentran varios en la base de datos, adicional no tengo claro que es lo que pasa... acá va el query...
Código PHP:
SELECT
mos_ap_member_company.id,
mos_ap_member_company.name,
mos_ap_member_company.email,
mos_ap_member_company.phone1,
mos_ap_member_company.phone2,
mos_ap_member_company.mailing_adress1,
mos_ap_member_company.mailing_adress2,
mos_ap_member_company.id_membership_category AS category,
mos_ap_contact.first_name AS contact_first_name,
mos_ap_contact.last_name AS contact_last_name,
mos_ap_member_billing.billing_month,
mos_ap_member_billing.billing_day,
mos_ap_service_head.billing_num,
( mos_ap_service_head.total - SUM( mos_ap_payment_billing.amount ) ) AS balance
FROM
mos_ap_member_company
LEFT JOIN
mos_ap_member_billing
ON
mos_ap_member_company.id = mos_ap_member_billing.id_member_company
LEFT JOIN
mos_ap_contact
ON
mos_ap_member_billing.id_primary_contact = mos_ap_contact.id
INNER JOIN
mos_ap_service_head
ON
mos_ap_member_company.id = mos_ap_service_head.id_member_company
INNER JOIN
mos_ap_payment_billing
ON
mos_ap_service_head.billing_num = mos_ap_payment_billing.billing_num
WHERE
1 = 1
AND
( mos_ap_service_head.total - (
SELECT
SUM( amount )
FROM
mos_ap_payment_billing
WHERE
billing_num = mos_ap_service_head.billing_num ) ) >= 0
AND
mos_ap_member_company.id IN (
SELECT
mos_ap_member_company.id
FROM
mos_ap_member_company
INNER JOIN
mos_ap_rates_table
ON
mos_ap_member_company.id_membership_category = mos_ap_rates_table.id_category_contact
INNER JOIN
mos_ap_service_head
ON
mos_ap_member_company.id = mos_ap_service_head.id_member_company
INNER JOIN
mos_ap_service_detail
ON
mos_ap_service_head.billing_num = mos_ap_service_detail.billing_num
WHERE
1 = 1
AND
mos_ap_rates_table.id_type_concept_billing = 1
)
GROUP BY
mos_ap_payment_billing.billing_num ASC
LIMIT 0, 200
Gracias!!!
