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

Nesting queries speed them up multiple times

Discussion in 'XenForo Development Discussions' started by Marcus, Aug 23, 2013.

  1. Marcus

    Marcus Well-Known Member

    The same query, nested into a father query to sort the results. This takes more than one second on my installation board.
    PHP:
    SELECT SQL_NO_CACHE thread.*

    FROM xf_thread_watch AS thread_watch
    INNER JOIN xf_thread 
    AS thread ON
    (thread.thread_id thread_watch.thread_id)
    LEFT JOIN xf_thread_read AS thread_read ON
    (thread_read.thread_id thread.thread_id
    AND thread_read.user_id 222)
    WHERE thread_watch.user_id 222
    AND thread.discussion_state "visible"

    AND thread.last_post_date thread_read.thread_read_date
    ORDER BY thread
    .last_post_date DESC
    And this nested query takes 0.018 seconds.
    PHP:
    SELECT SQL_NO_CACHE thread.*
    FROM (SELECT SQL_NO_CACHE thread.*
    FROM xf_thread_watch AS thread_watch
    INNER JOIN xf_thread 
    AS thread ON
    (thread.thread_id thread_watch.thread_id)


    LEFT JOIN xf_thread_read AS thread_read ON
    (thread_read.thread_id thread.thread_id
    AND thread_read.user_id 222)
    WHERE thread_watch.user_id 222
    AND thread.discussion_state "visible"

    AND thread.last_post_date thread_read.thread_read_datethread
    ORDER BY thread
    .last_post_date DESC
    Looks like the database starts sorting before the end results pop up. Is this behavior true for all mysql queries?
     
  2. Marcus

    Marcus Well-Known Member

    I changed my primary key from thread_id&title to thread_id and this query runs 30% faster now.
     
  3. tyteen4a03

    tyteen4a03 Well-Known Member

    As I said earlier, you can't just index everything.
     
  4. Marcus

    Marcus Well-Known Member

    The problem is here, that MySQL does not sort the results. Instead it sorts a lot of items before they are completely queried and limited.
     

Share This Page