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

slow query optimization

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

  1. Marcus

    Marcus Well-Known Member

    I have a query that INNER JOINs both xf_post on post_id and xf_thread on thread_id.

    SELECT table.*, xf_post.post_id is 3x faster than SELECT table.*, xf_thread.title

    Do you have any idea why that could be possible? The same is for xf_post.* which is three times faster than xf_thread.*
     
  2. tyteen4a03

    tyteen4a03 Well-Known Member

    One is indexed while the other isn't...
     
  3. Marcus

    Marcus Well-Known Member

    I also thought so, but then it is just in the SELECT area, I am not querying for xf_thread.title.

    You are right, I changed the primary xf_thread index to thread_id & title, the query is now 3 times faster. Do you know why that is? So you should have an index also for the stuff you put in SELECT?
     
    Last edited: Aug 12, 2013
  4. tyteen4a03

    tyteen4a03 Well-Known Member

    No, don't just index everything - indexes are for locating a specific record quicker.

    If your query is performance-demanding, I suppose you can get the thread id first then separately get the thread title, but then I'm no performance guru.
     
  5. Marcus

    Marcus Well-Known Member

    The index helps 30% of my pages to display them 0.15 seconds faster. Guess that's worth it and I don't mind the larger index size. I have no idea, why a index on the thread title helps, as I only query thread_id against my other tables.
     
  6. xf_phantom

    xf_phantom Well-Known Member

    Reminds me of
    [​IMG]


    :whistle:
     
    Marcus, MattW and tyteen4a03 like this.

Share This Page