here is my sql select statement, but it's running way too slow
the 11 subselects at the beginning are slowing the whole query down
Has anyone an idea how to replace the subselects in use by something else for a better performance?
Any help is welcome,
Julien
- Code: Select all
SELECT
c.photographer_serial_id,
c.photographer_id,
c.tarif_id,
c.pay_mode,
c.gender,
c.surname,
c.firstname,
c.name_profile,
c.zip_profile,
c.city_profile,
c.latitude,
c.longitude,
DATE_FORMAT(c.creation_timestamp,'%d.%m.%Y %H:%i') AS creation_timestamp,
DATE_FORMAT(c.last_edit_timestamp,'%d.%m.%Y %H:%i') AS last_edit_timestamp,
DATE_FORMAT(c.date_end,'%d.%m.%Y') AS date_end,
DATE_FORMAT(DATE_ADD(c.date_end,INTERVAL 1 DAY),'%d.%m.%Y') AS date_next_invoice,
c.date_end_del,
c.last_edit_user,
c.valid,
c.`deleted`,
c.comment,
(SELECT DATE_FORMAT(MAX(al.creation_timestamp),'%d.%m.%Y %H:%i') FROM photographers_login al WHERE al.photographer_id = c.photographer_id) AS last_login,
(SELECT MAX(al.creation_timestamp) FROM photographers_login al WHERE al.photographer_id = c.photographer_id) AS last_login_raw,
(SELECT COUNT(ai.invoice_id) FROM photographers_invoices ai WHERE (ai.payment_status = '0') AND (ai.photographer_id = c.photographer_id)) as payment_status,
(SELECT DATE_FORMAT(pi.valid_from,'%d.%m.%Y') FROM photographers_invoices pi WHERE (pi.photographer_id = c.photographer_id) AND (pi.invoice_id=(SELECT MAX(invoice_id) FROM photographers_invoices WHERE photographer_id=c.photographer_id)) ) as last_invoice_date,
(SELECT pi.price_brutto FROM photographers_invoices pi WHERE (pi.photographer_id = c.photographer_id) AND (pi.invoice_id=(SELECT MAX(invoice_id) FROM photographers_invoices WHERE photographer_id=c.photographer_id)) ) as last_price_brutto,
(SELECT pi.invoice_number FROM photographers_invoices pi WHERE (pi.photographer_id = c.photographer_id) AND (pi.invoice_id=(SELECT MAX(invoice_id) FROM photographers_invoices WHERE photographer_id=c.photographer_id)) ) as last_invoice_number,
(SELECT MAX(invoice_id) FROM photographers_invoices WHERE photographer_id=c.photographer_id) as last_invoice_id,
(SELECT ps.profile_views FROM photographers_statistics ps WHERE ps.photographer_id = c.photographer_id) as profile_views,
(SELECT ps.preview_views FROM photographers_statistics ps WHERE ps.photographer_id = c.photographer_id) as preview_views,
(SELECT ps.pdf_downloads FROM photographers_statistics ps WHERE ps.photographer_id = c.photographer_id) as pdf_downloads,
(SELECT ps.vcard_downloads FROM photographers_statistics ps WHERE ps.photographer_id = c.photographer_id) as vcard_downloads
FROM
photographers as c
INNER JOIN (SELECT photographer_id, MAX(photographer_serial_id) max_photographer_serial_id
FROM photographers
GROUP BY photographer_id
) AS b
ON c.photographer_id = b.photographer_id
AND c.photographer_serial_id = b.max_photographer_serial_id
".(isset($conditions) ? " WHERE (".join(") AND (", $conditions).")" : '')."
".(isset($conditions) ? "AND" : 'WHERE')."
c.photographer_id != 0
AND
c.`deleted`!=1
