Slow queries in customer service crashes server.

[jimmyc]

Hi I am getting 40 second page loads in the customer service page of the back office. It's putting too much stress on the server and leading to crashes.

 

 

here is the report:

 

 

SET timestamp=1402479743;
SELECT SQL_CALC_FOUND_ROWS
                 a.*
                ,
                        CONCAT(c.`firstname`," ",c.`lastname`) as customer, cl.`name` as contact, l.`name` as language, group_concat(message) as messages,
                        (
                                SELECT IFNULL(CONCAT(LEFT(e.`firstname`, 1),". ",e.`lastname`), "--")
                                FROM `ps_customer_message` cm2
                                INNER JOIN ps_employee e
                                        ON e.`id_employee` = cm2.`id_employee`
                                WHERE cm2.id_employee > 0
                                        AND cm2.`id_customer_thread` = a.`id_customer_thread`
                                ORDER BY cm2.`date_add` DESC LIMIT 1
                        ) as employee
                FROM `ps_customer_thread` a


                        LEFT JOIN `ps_customer` c
                                ON c.`id_customer` = a.`id_customer`
                        LEFT JOIN `ps_customer_message` cm
                                ON cm.`id_customer_thread` = a.`id_customer_thread`
                        LEFT JOIN `ps_lang` l
                                ON l.`id_lang` = a.`id_lang`
                        LEFT JOIN `ps_contact_lang` cl
                                ON (cl.`id_contact` = a.`id_contact` AND cl.`id_lang` = 1)

                WHERE 1
                GROUP BY cm.id_customer_thread

                ORDER BY a.id_customer_thread desc LIMIT 0,300;

 

 

Any idea on what has to be done to speed up this querie.

 

Thanks.

 

v 1.5.4.1