CODEWITHSUNDEEP
mysqlsql
user3550073
user3550073

Reputation: 33

Optimizing difficult mySQL query

I'm developing forum. Table struct is:

forum_threads:
  id: int unsigned, primary key
  forum_id: int unsigned
  flags: int unsigned. (1 - closed, 2 - important, etc.)
  views: int unsigned

forum_thread_views_count: (is a MEMORY table, needed to reduce IO operations)
  thread_id: int unsigned, primary key
  views: int unsigned

forum_posts:
  id: int unsigned, primary key
  thread_id: int unsigned
  author_id: int unsigned
  flags: int unsigned
  creation_date: int unsigned (unix timestamp)

I need to select: forum_thread.id, forum_thread.flags, forum_thread.views, forum_thread_views_count.views, COUNT(forum_posts), MIN(forum_posts.id), MAX(forum_posts.id) by forum_thread.forum_id ordered by forum_posts.creation_date DESC. First must be selected important themes (flags & 2).

Now i has query:

SELECT t.id, t.flags, t.views, tv.views, COUNT(p.id), MIN(p.id), MAX(p.id)
FROM forum_threads t
LEFT JOIN forum_thread_views_count tv ON tv.thread_id = t.id
LEFT JOIN forum_posts p ON p.thread_id = t.id
WHERE t.forum_id = 1
GROUP BY t.id
ORDER BY t.flags & 2 DESC, p.creation_date DESC;

this is works, but, i think, it can be made better.

Upvotes: 0

Views: 47

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20794

There are two ways to make it better. First, unless there is a valid reason for using outer joins, use inner joins. Next, change your group by from 

GROUP BY t.id

to 

GROUP BY t.id, t.flags, t.views, tv,views

While MySQL allows you to mismatch your select and group by clauses, it might cause you to get incorrect results.

Upvotes: 1

Related Questions