title: MySQL Join makes query slow - can't figure out why
body: <p>I am attaching results of two Explain statements for an old query and the newer version of that query.</p>
  
  <p>Do you see anything that does not make sense or looks wrong? The query became slow(4.5 seconds) after I added tm, tsa and tcd tables.</p>
  
  <p><a href="https://i.stack.imgur.com/2cZen.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2cZen.png" alt="enter image description here"></a></p>
  
  <p>Before those three tables were added to the query it was extremely fast (0.001 seconds). Here is what the explain looked like</p>
  
  <p><a href="https://i.stack.imgur.com/mnxDO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mnxDO.png" alt="enter image description here"></a></p>
  
  <p>tm table has four columns (tm_id (PK), owner_id, manager_id, status), tcd has three columns (tm_id, cd_id, created_date). tm_id and cd_id make a composite primary key and there is another index on cd_id. Same is the case with tsa with three columns (tm_id, smpa_id, created_date) with tm_id and smpa_id being a composite primary key and smpa_id has another index.</p>
  
  <p>What could be the reason for such slowness? </p>
  
  <p>old query: </p>
  
  <pre><code>SELECT upcm_id, COUNT( * ) 
  FROM user_post_content_master AS upcm
  JOIN content_deck AS cd ON cd.cd_id = upcm.cd_id
  JOIN social_media_post_account AS smpa ON smpa.smpa_id = upcm.smpa_id
  JOIN post_content_master AS pcm ON pcm.pcm_id = upcm.pcm_id
  WHERE smpa.user_id =2196
  AND upcm.upcm_post_date &gt;=1545891957
  AND upcm.upcm_status =1
  AND upcm.upcm_post_date &gt;=1546560000
  AND upcm.upcm_post_date &lt;=1546732799
  GROUP BY upcm.upcm_id
  ORDER BY upcm.upcm_post_date ASC
  </code></pre>
  
  <p>New Query:</p>
  
  <pre><code>SELECT upcm_id, COUNT( * ) 
  FROM user_post_content_master AS upcm
  JOIN content_deck AS cd ON cd.cd_id = upcm.cd_id
  JOIN social_media_post_account AS smpa ON smpa.smpa_id = upcm.smpa_id
  JOIN post_content_master AS pcm ON pcm.pcm_id = upcm.pcm_id
  JOIN team_content_deck AS tcd ON ( tcd.cd_id = upcm.cd_id ) 
  JOIN team_social_account AS tsa ON tsa.smpa_id = upcm.smpa_id
  JOIN team_members AS tm ON tm.team_member_id = tsa.team_member_id
  AND tm.team_member_id = tcd.team_member_id
  AND tm.owner_id =2196
  AND tm.manager_id =2196
  AND tm.status =1
  WHERE smpa.user_id =2196
  AND upcm.upcm_post_date &gt;=1545891957
  AND upcm.upcm_status =1
  AND upcm.upcm_post_date &gt;=1546560000
  AND upcm.upcm_post_date &lt;=1546732799
  GROUP BY upcm.upcm_id
  ORDER BY upcm.upcm_post_date ASC
  </code></pre>
  
  <p>If I remove the conditions from the tm table, it is fast again. Nothing changed in the joins though.</p>
  
  <pre><code>EXPLAIN SELECT upcm_id, COUNT( * ) 
  FROM user_post_content_master AS upcm
  JOIN content_deck AS cd ON cd.cd_id = upcm.cd_id
  JOIN social_media_post_account AS smpa ON smpa.smpa_id = upcm.smpa_id
  JOIN post_content_master AS pcm ON pcm.pcm_id = upcm.pcm_id
  JOIN team_content_deck AS tcd ON ( tcd.cd_id = upcm.cd_id ) 
  JOIN team_social_account AS tsa ON tsa.smpa_id = upcm.smpa_id
  JOIN team_members AS tm ON tm.team_member_id = tsa.team_member_id
  AND tm.team_member_id = tcd.team_member_id
  WHERE smpa.user_id =2196
  AND upcm.upcm_post_date &gt;=1545891957
  AND upcm.upcm_status =1
  AND upcm.upcm_post_date &gt;=1546560000
  AND upcm.upcm_post_date &lt;=1546732799
  GROUP BY upcm.upcm_id
  ORDER BY upcm.upcm_post_date ASC
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/sV1yW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sV1yW.png" alt="enter image description here"></a></p>