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

[Jean-Baptiste]

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 :

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 :

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]

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:

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.

 

[Jean-Baptiste]

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:

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 ?

 

[Luke F]

Order by post_id instead of date

 

[Chris D]

Thanks you Chris I understand !

So I just need not to order by post_date ?

If you don't have to then no. Ordering by post_id works as @Luke Foreman suggests. It's essentially the same thing as ordering by the date.

 

[Jean-Baptiste]

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:

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 :

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]

What is the limit and offset you're passing to the query?

 

[Jean-Baptiste]

What is the limit and offset you're passing to the query?

I have just changed from INNER JOIN to LEFT JOIN, and the run time is a lot better : 0.7 seconds, but that's still huge for a query !

 

[Jean-Baptiste]

What is the limit and offset you're passing to the query?

20 and 40 :

 

[Jean-Baptiste]

What is the limit and offset you're passing to the query?

Ok Chris I found the problem : it's because of my custom param I added to xf_post.

WHERE xf_post.custom_param = 1

Anyway to fix this ?

 

[Chris D]

Why didn't I pick up on that sooner?

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.

 

[Jean-Baptiste]

Why didn't I pick up on that sooner?

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]

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

 

[Jean-Baptiste]

It's still worth adding an index

Great thanks Luke !