title: Slow query in MySQL with joins
body: <p>I have a query that runs too slow but I can't figure out the reason behind it, or a solution to it. The query took around <strong>22 sec</strong> to run at first, but then I tried to remove a foreign key in the table Book that is a FK to ParentID in the same table. After this removal the query took around <strong>1 sec</strong>. This is of course good but there should be room for even more improvements, either restructure the query itself or by using different indexes.</p>
  
  <p>Can anyone advise? Why does the foreign key to Book.ParentID cause such an increase in query time? What is the best approach here, try to rewrite the query or look into the indexes?</p>
  
  <p>Except for dropping the foreign key to <code>Book.ParentID</code>, I've tried using <code>use index</code> on the selected table and some of the joins but with no real luck.</p>
  
  <p>Here's a fiddle: <a href="http://sqlfiddle.com/#!9/7f7bbb/9" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/7f7bbb/9</a></p>
  
  <p>However, the explain plan in the fiddle differ from the one I get in production. This is the explain plan from production (with the foreign key to Book.ParentID):</p>
  
  <pre><code>'1','SIMPLE','Bk',NULL,'index_merge','PRIMARY,fk_CategoryParent_ID_idx','PRIMARY,fk_CategoryParent_ID_idx','8,9',NULL,'2','100.00','Using union(PRIMARY,fk_CategoryParent_ID_idx); Using where; Using temporary; Using filesort'
  '1','SIMPLE','CGB2',NULL,'ref','uq_ChapterGroup_Category,book_chaptergroup_unique_row_constraint,fk_Category_ChapterGroup_ID,ix_chapterGroupBook_chapterGroupID','book_chaptergroup_unique_row_constraint','9','Hypo.Bk.ID','8','100.00','Using where'
  '1','SIMPLE','CCG2',NULL,'ref','uq_ChapterID_ChapterGroupItemInChapterGroup,uq_ExamID_ChapterGroupItemInChapterGroup,fk_ChapterGroup_Chapter_ID,ix_chapterChapterGroup_chapterID','fk_ChapterGroup_Chapter_ID','8','Hypo.CGB2.ChapterGroupID','2','100.00','Using where'
  '1','SIMPLE','ATM',NULL,'ref','PRIMARY,fk_Chapter_AnswerTextMarker_ID,fk_QuestionGroup_AnswerTextMarker_ID','fk_Chapter_AnswerTextMarker_ID','8','Hypo.CCG2.ChapterID','5','100.00','Using where'
  '1','SIMPLE','QG',NULL,'eq_ref','PRIMARY','PRIMARY','8','Hypo.ATM.QuestionGroupID','1','100.00','Using index'
  '1','SIMPLE','AQS',NULL,'ref','PRIMARY,fk_AQSession_AnswerTextMarker_ID,ix_answerQuestionSession_questionSessionID','fk_AQSession_AnswerTextMarker_ID','8','Hypo.ATM.ID','313','100.00',NULL
  '1','SIMPLE','QS',NULL,'eq_ref','PRIMARY,fk_QSession_User_ID,ix_QuestionSession_userId_type_sessionDate','PRIMARY','8','Hypo.AQS.QuestionSessionID','1','5.00','Using where'
  </code></pre>
  
  <p><strong>Row count in my environment:</strong></p>
  
  <p>AnswerQuestionSession 3992125<br>
  QuestionSession 367334<br>
  AnswerTextMarker 9696<br>
  QuestionGroup 18793<br>
  ChapterGroupItemInChapterGroup 42360<br>
  ChapterGroupBook 2297<br>
  Book 378  </p>