1. 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)

Discussion in 'XenForo Development Discussions' started by allewreK, Nov 9, 2013.

  1. allewreK

    allewreK Active Member

    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.
     
  2. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
  3. allewreK

    allewreK Active Member

    Thanks you Chris I understand !

    So I just need not to order by post_date ?
     
  4. Luke F

    Luke F Well-Known Member

    Order by post_id instead of date
     
    Chris D likes this.
  5. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
  6. allewreK

    allewreK Active Member

    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)
     
  7. Chris D

    Chris D XenForo Developer Staff Member

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

    allewreK Active Member

    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 :mad: !
     
  9. allewreK

    allewreK Active Member

    20 and 40 :
    1.png
     
  10. allewreK

    allewreK Active Member

    Ok Chris I found the problem : it's because of my custom param I added to xf_post.
    Code:
    WHERE xf_post.custom_param = 1
    Anyway to fix this ?
     
  11. Chris D

    Chris D XenForo Developer Staff Member

    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 likes this.
  12. allewreK

    allewreK Active Member

    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) ?
     
  13. Luke F

    Luke F Well-Known Member

    It's still worth adding an index
     
    allewreK likes this.
  14. allewreK

    allewreK Active Member

    Great thanks Luke !
     

Share This Page