I have a specific situation that requires a specific set of results from a MySQL query, first look at the current query, and then ask my question:
SELECT thread.dateline AS tdateline, post.dateline AS pdateline, MIN(post.dateline) FROM thread AS thread LEFT JOIN post AS post ON(thread.threadid = post.threadid) LEFT JOIN forum AS forum ON(thread.forumid = forum.forumid) WHERE post.postid != thread.firstpostid AND thread.open = 1 AND thread.visible = 1 AND thread.replycount >= 1 AND post.visible = 1 AND (forum.options & 1) AND (forum.options & 2) AND (forum.options & 4) AND forum.forumid IN(1,2,3) GROUP BY post.threadid ORDER BY tdateline DESC, pdateline ASC
As you can see, basically I need to select the dateline of the threads from the "thread" table, in addition to the dateline of the second post of each thread, that’s all under the conditions that you see in WHERE CLAUSE. Since each thread has many messages, and I need only one result for the stream, for this purpose I used GROUP BY CLAUSE.
In this request, only one post-date will be returned with a corresponding unique stream.
My questions:
- How to limit returned threads to each forum !? Suppose I need only 5 threads — the maximum returned for each forum declared in the WHERE CLAUSE 'forum.forumid IN (1,2,3)', how this can be achieved.
- Are there any recommendations for optimizing this query (of course, after solving the first point)?
Notes:
- I prefer not to use subqueries, but if this is the only solution available I will agree. Double queries are not recommended. I am sure that there is a smart solution for this situation.
- I am using MySQL 4.1+, but if you know the answer to another engine, just share.
Recognized advice in advance :)
source share