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

Optimising a query

Discussion in 'XenForo Development Discussions' started by Robust, Jan 31, 2016.

  1. Robust

    Robust Well-Known Member

    The following query takes around 4.5 seconds to run on a big board apparently. It's used to fetch data for a profile tab, it's loaded deferred of course, but 4.5 seconds is a while. How can I optimise it?

    Code:
    SELECT post.*, thread.title, thread.thread_id, thread.bestanswer FROM xf_post AS post
    LEFT JOIN xf_thread AS thread
    ON (thread.thread_id = post.thread_id)
    WHERE post.user_id = ?
    AND post.ba_votes >= 1
    ORDER BY post.post_date DESC
    LIMIT 10
     
  2. katsulynx

    katsulynx Well-Known Member

    It does grant a visible speed improvement to remove the unnecessary join on my test board with very small tables (From 0.0037 seconds down to 0.0033). You may want to give it a try to see if that improvement scales up:
    Code:
    SELECT post.*, thread.title, thread.thread_id, thread.bestanswer
    FROM xf_post AS post, xf_thread as thread
    WHERE post.user_id = ? AND post.ba_votes >= 1 AND thread.thread_id = post.thread_id
    ORDER BY post.post_date DESC
    LIMIT 10
     
    Robust likes this.
  3. Robust

    Robust Well-Known Member

    I'll give it a shot - I can see how it takes longer on a big board but 4.5 seconds is extreme, definitely some optimisation needed. Luckily it is deferred loading so it's not affecting the page load itself. A few other big boards that were tried on didn't have the same problem, however.
     
  4. Daniel Hood

    Daniel Hood Well-Known Member

    When in doubt, EXPLAIN your query.

    Code:
    EXPLAIN SELECT post.*, thread.title, thread.thread_id, thread.bestanswer FROM xf_post AS post
    LEFT JOIN xf_thread AS thread
    ON (thread.thread_id = post.thread_id)
    WHERE post.user_id = ?
    AND post.ba_votes >= 1
    ORDER BY post.post_date DESC
    LIMIT 10
    Should output a nice breakdown of what's happening, what indexes are being used.
     
    Robust, Itworx4me and thedude like this.
  5. Xon

    Xon Well-Known Member

    For performance reasons, really try to avoid these sorts of searches against the xf_post table.

    Try creating a xf_post_bestanswer table and update it when something is entered, while keeping it as small as possible. You can then apply custom indexes without requiring indexing the entire xf_post table.

    The xf_post part of query is very inefficient!

    Cutting the query down to:
    Code:
    explain
    SELECT post.*
    FROM xf_post AS post
    WHERE post.user_id = 3306
    ORDER BY post.post_date DESC
    LIMIT 10
    
    (just adding an ID to test)

    This outputs:
    Code:
    # id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
    '1', 'SIMPLE', 'post', 'ref', 'user_id', 'user_id', '4', 'const', '1537', 'Using where; Using filesort'
    
    Note; user_id isn't in the order by clause, this is the filesort being done after the where/user_id search.

    ba_votes is a custom column, so I really doubt it is in an index making it slower.

    Another problem is MySQL does eager joins, meaning it is pulling the entire contents of your select for every matching row. The solution is to ensure the indexes match well, and use tricks like the following to reduce the data being read:
    Code:
    select post.*, thread.title, thread.thread_id, thread.bestanswer
    from
    (
    SELECT post.post_id
    FROM xf_post AS post
    WHERE post.user_id = ? AND post.ba_votes >= 1
    ORDER BY post.post_date DESC
    LIMIT 10
    ) data
    JOIN xf_post AS post ON (data.post_id = post.post_id)
    JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
    
    No point doing a LEFT JOIN on xf_thread, just doing a JOIN.

    Also try to select less data from the post table. Do you really need every field?
     
    Kirk, Robust, Itworx4me and 2 others like this.

Share This Page