MySQL Get most replyed threads in X days join threads take about 1 second
<p>I want to display the 10 threads of a vbulletin based forum which had most replys this week. So I wrote a query to count all the posts in a timespan grouped by the threadid to make sure that every thread is only once in this list:</p>

<pre><code>SELECT post.postid, COUNT(post.postid) AS replys
FROM post
WHERE post.visible = 1
AND post.dateline &gt; DATE_SUB("2016-01-21", INTERVAL - 7 DAY)
GROUP BY post.threadid
ORDER BY replys DESC, post.dateline DESC
LIMIT 10
</code></pre>

<p>This query took about 0.3 seconds on a database where <em>posts</em> have about 400.000 rows and <em>threads</em> about 40.000 rows, which seems a bit long, but is still acceptable - also because it can be cached.</p>

<p>But I also need some information of the <em>thread</em> table like the titel, which requires a JOIN:</p>

<pre><code>SELECT post.postid, COUNT(post.postid) AS replys,
thread.title, thread.views, thread.replycount 
FROM post
INNER JOIN thread ON(thread.threadid = post.threadid)
WHERE post.visible = 1
AND post.dateline &gt; DATE_SUB("2016-01-21", INTERVAL - 7 DAY)
GROUP BY post.threadid
ORDER BY replys DESC, post.dateline DESC
LIMIT 10
</code></pre>

<p>Now my query tooks about 1 second which is quite too much! What can I do to make the query faster? </p>

<p>Information about the structure of the tables: </p>

<p><strong>Post</strong></p>

<p><a href="https://i.stack.imgur.com/BN85F.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BN85F.png" alt="enter image description here"></a>
<a href="https://i.stack.imgur.com/RZeDs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RZeDs.png" alt="enter image description here"></a></p>

<p><strong>Thread</strong></p>

<p><a href="https://i.stack.imgur.com/pJ0bQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pJ0bQ.png" alt="enter image description here"></a>
<a href="https://i.stack.imgur.com/LSEuP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LSEuP.png" alt="enter image description here"></a></p>