• 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

Marcus

Well-known member
#1
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_date) thread
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?
 

Marcus

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