"queries under the database are not optimized" ???

[CivilWarTalk]

Contacted my tech at my host today because my site has been sluggish, loads were a bit high (from 1.0 to 2.0, normal would be 0.2 to 0.6), and there seemed to be unusual lag even though my user count and activity was at normal levels...

The tech sent this message back:

I noticed that the queries under the database "cwt" are not optimized and are responsible for the increase in the server load. Please see the sample queries below.

---
| 2708 | cwt | localhost | admin_xenforo | Execute | 5 | Sorting result | SELECT post.*
,
user.*, IF(user.username IS NULL, post.username, user.username) AS username,
user_profile.*,
0 AS like_date
FROM xf_post AS post

LEFT JOIN xf_user AS user ON
(user.user_id = post.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = post.user_id)
WHERE post.thread_id = '71181'
AND (post.position >= 0 AND post.position < 20)
AND (post.message_state IN ('visible'))
ORDER BY post.position ASC, post.post_date ASC |
| 2711 | cwt | localhost | admin_xenforo | Execute | 4 | statistics | SELECT post.*
,
user.*, IF(user.username IS NULL, post.username, user.username) AS username,
user_profile.*,
0 AS like_date
FROM xf_post AS post

LEFT JOIN xf_user AS user ON
(user.user_id = post.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = post.user_id)
WHERE post.thread_id = '23218'
AND (post.position >= 0 AND post.position < 20)
AND (post.message_state IN ('visible'))
ORDER BY post.position ASC, post.post_date ASC |
| 2714 | cwt | localhost | admin_xenforo | Execute | 2 | Sorting result | SELECT post.*
,
user.*, IF(user.username IS NULL, post.username, user.username) AS username,
user_profile.*,
0 AS like_date
FROM xf_post AS post

LEFT JOIN xf_user AS user ON
(user.user_id = post.user_id)
LEFT JOIN xf_user_profile AS user_profile ON
(user_profile.user_id = post.user_id)
WHERE post.thread_id = '7178'
AND (post.position >= 0 AND post.position < 20)
AND (post.message_state IN ('visible'))
ORDER BY post.position ASC, post.post_date ASC |
---

I would strongly recommend you to optimize these scripts. Please contact your developer and optimize these scripts immediately.

Any thoughts about this?

 

[CivilWarTalk]

Just a few other items to mention, I'm running XenForo 1.1.4

I have the following 9 addons active:

AzuCloud 0.1.2
IwDRP Total Forum Thread Views 1.02
MailChimp 0.9.1
Sitemap for XenForo 1.1.2
TaigaChat Pro 1.0.3
[8wayRun.Com] XenPorta (Portal) 1.5.5
[bd] Forum Watch 1.1
[bd] Tag Me 1.6.5
[Tinhte] Attach Image Optimization 2.0.1

 

[digitalpoint]

Take one of those queries and run an EXPLAIN on it in MySQL... for example, what does this output?

EXPLAIN SELECT post.* , user.*, IF(user.username IS NULL, post.username, user.username) AS username, user_profile.*, 0 AS like_date FROM xf_post AS post LEFT JOIN xf_user AS user ON (user.user_id = post.user_id) LEFT JOIN xf_user_profile AS user_profile ON (user_profile.user_id = post.user_id) WHERE post.thread_id = '71181' AND (post.position >= 0 AND post.position < 20) AND (post.message_state IN ('visible')) ORDER BY post.position ASC, post.post_date ASC;

It should not take anywhere remotely close to that long to sort a few records (20).