Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

subselects

Codes here !

Moderators: macek, egami, gesf

subselects

Postby jumioo » Thu Nov 29, 2012 5:10 am

Hello all,
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
jumioo
New php-forum User
New php-forum User
 
Posts: 1
Joined: Fri Jul 02, 2010 6:39 am

Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron