slow query optimization

[Marcus]

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.*

 

[tyteen4a03]

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

 

[Marcus]

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?

 

[tyteen4a03]

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]

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.

 

[xf_phantom]

I have no idea, why a index on the thread title helps

Reminds me of