Nesting queries speed them up multiple times

[Marcus]

The same query, nested into a father query to sort the results. This takes more than one second on my installation board.

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.

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]

I changed my primary key from thread_id&title to thread_id and this query runs 30% faster now.

 

[tyteen4a03]

I changed my primary key from thread_id&title to thread_id and this query runs 30% faster now.

As I said earlier, you can't just index everything.

 

[Marcus]

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.