Optimising a query

[Robust]

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?

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

 

[Lukas W.]

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:

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]

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:

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

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.

 

[Daniel Hood]

When in doubt, EXPLAIN your query.

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.

 

[Xon]

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 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?

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

The xf_post part of query is very inefficient!

Cutting the query down to:

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:

# 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:

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?