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

slow query optimization

Marcus

Well-known member
#1
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.*
 

Marcus

Well-known member
#3
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:

tyteen4a03

Well-known member
#4
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?
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.
 

Marcus

Well-known member
#5
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.