I am trying to create SQL for retrieveing a list of latests posts for the forum thread. I have the following code:
SELECT item_discuss_thread_id , item_discuss_post_title , COUNT(item_discuss_thread_id) AS nb_posts FROM item_discuss_posts GROUP BY item_discuss_thread_id
Obviously this will group without the respect of if the post is latest or not.
item_discuss_post_title will just get the first row in the group.
I wonder if there's some way around this? If not, what is the best way to solve the problem... only subqueries?
UPDATE: Please note that I need all threads, LIMIT 1 is not solving the problem. Also ORDER BY is not an option as GROUP BY will select the first record from group anyway. This is not such a simple question as it can seem to be.
I really want to try to avoid using subqueries or if doing so - use it the optimal may. What I came with currently is something like this:
SELECT ordered_by_date.item_discuss_thread_id , item_discuss_post_title , COUNT(item_discuss_thread_id) AS nb_posts FROM ( SELECT item_discuss_thread_id , item_discuss_post_title FROM item_discuss_posts ORDER BY item_discuss_post_datetime DESC ) AS ordered_by_date GROUP BY item_discuss_thread_id
EXPLAIN EXTENDED gives the following result:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 1, PRIMARY, <derived2>, ALL, \N, \N, \N, \N, 20, Using temporary; Using filesort 2, DERIVED, item_discuss_posts, index, \N, item_discuss_post_datetime, 8, \N, 20,
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `item_discuss_posts` ADD INDEX `item_posts_idx_item_id` (`item_discuss_thread_id`);
SELECT item_discuss_posts.item_discuss_thread_id, item_discuss_posts.item_discuss_post_title, COUNT(item_discuss_posts.item_discuss_thread_id) AS nb_posts FROM item_discuss_posts GROUP BY item_discuss_posts.item_discuss_thread_id ORDER BY NULL