• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Why is my query so slow ? (25 seconds to be run)

allewreK

Active member
#1
Hello,

I made a query, that fetch a post and it's associated thread and user. But when I run it on my production board it's taking ages to load, the debug mode show 10 to 25 seconds on this query.

I don't have any problems on my developement forum, but I do on my big board.

This morning, I switched all my tables to InnoDB (the tables that needed to be like that, because years ago when I switch from vBulletin the import made my tables MyISAM). Could InnoDB be the reason ?

Here is my code :

PHP:
public function getTopicsBy($by = "custom_param", $start = 0, $stop = 30)
    {
        $threads = $this->_getDb()->fetchAll("
            SELECT    xf_post.*, xf_user.*, xf_thread.*
            FROM    xf_post
                INNER JOIN xf_user ON (xf_user.user_id = xf_post.user_id)
                INNER JOIN xf_thread ON (xf_thread.thread_id = xf_post.thread_id)
            WHERE    xf_post.custom_param    = 1
            ORDER BY xf_post.post_date DESC
            LIMIT ?, ?
        ", array($start, $stop));
      
        return $threads;
    }
Query :
Code:
SELECT    xf_post.*, xf_user.*, xf_thread.*
FROM    xf_post
INNER JOIN xf_user ON (xf_user.user_id = xf_post.user_id)
INNER JOIN xf_thread ON (xf_thread.thread_id = xf_post.thread_id)
WHERE    xf_post.custom_param    = 1
ORDER BY xf_post.post_date DESC
LIMIT ?, ?
Best regards.
 

Chris D

XenForo developer
Staff member
#2
You're ordering by post_date but there is no index on the post_date column by default.

If you have a considerable number of posts you should run the following SQL query to add the index:

Code:
ALTER TABLE `xf_post` 
ADD INDEX `post_date` (`post_date` ASC);
If you execute this on a live board it will adversely effect performance and probably timeout in PHP My Admin while the query is running.
 

allewreK

Active member
#3
You're ordering by post_date but there is no index on the post_date column by default.

If you have a considerable number of posts you should run the following SQL query to add the index:

Code:
ALTER TABLE `xf_post`
ADD INDEX `post_date` (`post_date` ASC);
If you execute this on a live board it will adversely effect performance and probably timeout in PHP My Admin while the query is running.
Thanks you Chris I understand !

So I just need not to order by post_date ?
 

allewreK

Active member
#6
Order by post_id instead of date
You're ordering by post_date but there is no index on the post_date column by default.

If you have a considerable number of posts you should run the following SQL query to add the index:

Code:
ALTER TABLE `xf_post`
ADD INDEX `post_date` (`post_date` ASC);
If you execute this on a live board it will adversely effect performance and probably timeout in PHP My Admin while the query is running.
Same problem ordering by post_id :

Code:
SELECT    xf_post.*, xf_user.*, xf_thread.*
FROM    xf_post
    INNER JOIN xf_user ON (xf_user.user_id = xf_post.user_id)
    INNER JOIN xf_thread ON (xf_thread.thread_id = xf_post.thread_id)
WHERE    xf_post.post_custom_param    = 1
ORDER BY xf_post.post_id DESC
LIMIT ?, ?
(7 seconds run time)
 

Chris D

XenForo developer
Staff member
#11
Why didn't I pick up on that sooner? :confused:

Code:
ALTER TABLE `xf_post`
ADD INDEX `custom_param` (`custom_param` ASC);
Add an index to the custom_param field.

Any column that features regularly in a WHERE or ORDER clause should have an index.
 

allewreK

Active member
#12
Why didn't I pick up on that sooner? :confused:

Code:
ALTER TABLE `xf_post`
ADD INDEX `custom_param` (`custom_param` ASC);
Add an index to the custom_param field.

Any column that features regularly in a WHERE or ORDER clause should have an index.
Ok thanks you Chris. I think I'm going to do this, but since I'm using this query on 1 page out of thousands normal threads, would you suggest me to keep that special page with the param as a SQL index, or not doing this modification that is not usefull (for performances, so that don't affect posts and threads) ?
 

Luke F

Well-known member
#13
Ok thanks you Chris. I think I'm going to do this, but since I'm using this query on 1 page out of thousands normal threads, would you suggest me to keep that special page with the param as a SQL index, or not doing this modification that is not usefull (for performances, so that don't affect posts and threads) ?
It's still worth adding an index