Can anyone identify this query?

R

RobParker

Well-known member
We're very busy today and it's causing a few server issues. Our host asked us if we could identify what was causing the following:

Copying to tmp table | SELECT xf_thread.*, xf_user.*, xf_post.message, xf_post.attach_count, xf_node.title AS node_title,

Any ideas?
 
Sort by date Sort by votes
Cheers, our hosts found it too. We've currently got around 3000 users online and that query was killing the server. Disabling the recent news block and it got a lot better.

Is there anything obviously "wrong" with it? Inefficient, etc?
 
Upvote 0 Downvote
I searched for that query in the XF files using OS X's search feature. Nothing came up, but I noticed it was flagging files outside of the XF directory. Those files were from XenPorta in a different directory on my hard drive.

RobParker said:
Is there anything obviously "wrong" with it? Inefficient, etc?
Click to expand...

Well it is a big query with lots of joins. It might benefit from some extra indexes in your schema.
 
Upvote 0 Downvote
Yep they were all set to cache a few hours.

It seems that the recentnews block was trying to create a lot of tmp tables which was causing IO issues.

Today is our busiest day of the year by a long way so we expected high traffic.
 
Upvote 0 Downvote
RobParker,

I'm glad you've found this dude.

We have sporadic days where we have 1000+ users online and we were also finding XenPorta was killing our site to the point where we turn it off completely.

Definitely need to resolve this problem. What does the query actually do? Is it necessary? I guess we should post in the XenPorta support thread first.

EDIT: The answer to the question "is it necessary" is: yes. Yes if you want it to display any news at all :p

PHP: 
$news = $this->_getDb()->fetchAll("
SELECT xf_thread.*, xf_user.*, xf_post.message, xf_post.attach_count, xf_node.title AS node_title, 
IF(xf_user.username IS NULL, xf_thread.username, xf_user.username) AS username,
IF(EWRporta_promotes.promote_date IS NULL, xf_thread.post_date, EWRporta_promotes.promote_date) AS promote_date,
EWRporta_promotes.promote_icon, EWRporta_promotes.promote_data
FROM xf_thread
LEFT JOIN xf_user ON (xf_user.user_id = xf_thread.user_id)
INNER JOIN xf_post ON (xf_post.post_id = xf_thread.first_post_id)
INNER JOIN xf_node ON (xf_node.node_id = xf_thread.node_id)
LEFT JOIN EWRporta_promotes ON (EWRporta_promotes.thread_id = xf_thread.thread_id)
$fromWhere
WHERE (xf_thread.node_id IN (".$this->_getDb()->quote($options['forum']).") OR EWRporta_promotes.promote_date < ?)
AND xf_thread.discussion_state = 'visible'
AND IF(EWRporta_promotes.promote_date IS NULL, xf_thread.post_date, EWRporta_promotes.promote_date) < ?
ORDER BY $fromOrder promote_date DESC
LIMIT ?, ?
", array(XenForo_Application::$time, XenForo_Application::$time, $page, $options['limit']));
 
Upvote 0 Downvote
Chris,

It would be amazing if you could resolve this somehow. We've just turned xenporta off completely and page loads with 3000+ users has gone down from 5 seconds to 0.5 seconds.

If there's anything I can do to help let me know.

Cheers

Rob
 
Upvote 0 Downvote
Andy.N said:
I'm currently styling Widget Page to replace my Xenporta homepage.

Still have some styling issues to fix but the functions are there. @xfrocks is doing a great job to develop BD WF into a powerful addon.
Click to expand...
Yes exactly. :)
When Widget Page/Portal becomes stable and well documented.
I will be going to use that also as my Index :)
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

ahm340
  • Question Question
XF 2.0 Can anyone identify this query? ... xf_user_User_1
Replies
2
Views
543
ahm340
ahm340
Back
Top Bottom