Customer Service page is extremely slow

[digital]

I have a Prestashop 1.5.6.2 installation that is running very smooth except for one thing. When I click on the Customer Service tab, it takes 1.5 minutes for it to load. I only have about 3000 threads. (On the same server i have another shop, almost identical but with more than 6000 threads that needs ~1 second to load the same page)

 

Everything else on the site runs fast.

 

I am logging slow queries and got this in mysql-slow.log:

/usr/sbin/mysqld, Version: 5.5.38-0+wheezy1-log ((Debian)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 141009  8:38:57
# User@Host: presta1562[presta1562] @ localhost []
# Query_time: 85.514298  Lock_time: 0.000276 Rows_sent: 300  Rows_examined: 74560963
use presta1562;
SET timestamp=1412836737;
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 psmr147_customer_message cm2 INNER JOIN psmr147_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 `psmr147_customer_thread` a
			
			
		LEFT JOIN `psmr147_customer` c ON c.`id_customer` = a.`id_customer`
		LEFT JOIN `psmr147_customer_message` cm ON cm.`id_customer_thread` = a.`id_customer_thread`
		LEFT JOIN `psmr147_lang` l ON l.`id_lang` = a.`id_lang`
		LEFT JOIN `psmr147_contact_lang` cl ON (cl.`id_contact` = a.`id_contact` AND cl.`id_lang` = 20) 
			WHERE 1  AND cl.`id_contact` = 1 
			GROUP BY cm.id_customer_thread 
			
			ORDER BY `date_upd` DESC
			LIMIT 0,300;

I have copied the site to a new server to rule out server problems, but the loading time stays the same.

 

I can't upgrade to a newer version due to module compatibility. I did however have the same problem on 1.4 and it did not get better by upgrading to 1.5+.

 

I would be very thankful for any help on this problem.

[DARKF3D3]

Did you managed to fix it?

This problem it's still present on PS1.7.4.2.

 

[digital]

Yes, I finally fixed it. I added indexes to the slow tables. Don't remember exactly how I did it, but that solved the problem.