title: Why does this "Mysql" query hangs once a while?
body: <p>I'm working with complex <code>MySQL query</code> to fetch data over <code>Key,Value</code> table. IN order to achieve that we are doing multiply <code>self joins</code>. At the beginning we thought that the issue is <code>bit-wise</code> operations in <code>MySQL</code> as they cannot be done with <code>indexes</code>. <strong>But</strong> The issue still exists without using <code>bit-wise</code> operations. i.e the same happens if changing all <code>bit-wise OR</code> to <code>equal (==)</code></p>
  
  <h2>Query</h2>
  
  <p>The following <code>query</code> usually runs around 500 Milliseconds . But <strong>once a while</strong>  <code>MySQL</code> runs this query over <strong>10 seconds!!</strong> and even more..</p>
  
  <pre><code>SELECT DISTINCT sometable1_.id AS col_0_0_,
              sometable1_.hebrewName AS col_1_0_,
              sometable1_.urlBestBigPhoto AS col_2_0_
  FROM sometable1 sometable1_
  INNER JOIN sometable2 characters1_ ON sometable1_.id=characters1_.term_id
  INNER JOIN sometable2 characters2_ ON sometable1_.id=characters2_.term_id
  INNER JOIN sometable2 characters3_ ON sometable1_.id=characters3_.term_id
  INNER JOIN sometable2 characters4_ ON sometable1_.id=characters4_.term_id
  INNER JOIN sometable2 characters5_ ON sometable1_.id=characters5_.term_id
  INNER JOIN sometable2 characters6_ ON sometable1_.id=characters6_.term_id
  INNER JOIN sometable2 characters7_ ON sometable1_.id=characters7_.term_id
  INNER JOIN sometable2 characters8_ ON sometable1_.id=characters8_.term_id
  INNER JOIN sometable2 characters9_ ON sometable1_.id=characters9_.term_id
  WHERE sometable1_.category="ABC"
  AND (characters2_.value = 1&lt;&gt;0
     OR characters2_.value=0)
  AND characters2_.name="somevalue2"
  AND (characters3_.value &amp; 2&lt;&gt;0
     OR characters3_.value=0)
  AND characters3_.name="somevalue3"
  AND (characters4_.value = 128&lt;&gt;0
     OR characters4_.value=0)
  AND characters4_.name="somevalue4"
  AND (characters5_.value = 1&lt;&gt;0
     OR characters5_.value=0)
  AND characters5_.name="somevalue5"
  AND (characters6_.value = 16392&lt;&gt;0
     OR characters6_.value=0)
  AND characters6_.name="somevalue6"
  AND (characters7_.value = 1&lt;&gt;0     OR characters7_.value=0)
  AND characters7_.name="somevalue7"
  AND (characters8_.value = 256&lt;&gt;0     OR characters8_.value=0)
  AND characters8_.name="somevalue8"
  AND (characters9_.value = 124&lt;&gt;0    OR characters9_.value=0)
  AND characters9_.name="somevalue9"
  AND (characters1_.name="somevalue10"  OR characters1_.name="somevalue11")
  GROUP BY sometable1_.id,
       characters9_.term_id,
       characters8_.term_id,
       characters7_.term_id,
       characters6_.term_id,
       characters5_.term_id,
       characters4_.term_id,
       characters3_.term_id,
       characters2_.term_id
       ORDER BY sum(characters1_.value) DESC, sometable1_.text ASC LIMIT 10
  </code></pre>
  
  <h2>Mysql explain</h2>
  
  <p>After using explain we get </p>
  
  <pre><code>╔════╦═════════════╦═══════════════╦════════╦═════════════════════╦═════════╦═════════╦══════════════════════╦══════╦═════════════════════════════════════════════════════════════════════╗
  ║ id ║ select_type ║     table     ║  type  ║    possible_keys    ║   key   ║ key_len ║         ref          ║ rows ║                                Extra                                ║
  ╠════╬═════════════╬═══════════════╬════════╬═════════════════════╬═════════╬═════════╬══════════════════════╬══════╬═════════════════════════════════════════════════════════════════════╣
  ║  1 ║ SIMPLE      ║ characters1_  ║ range  ║ FK_47,name          ║ name    ║      77 ║ NULL                 ║ 2146 ║ Using index condition; Using where; Using temporary; Using filesort ║
  ║  1 ║ SIMPLE      ║ sometable1    ║ eq_ref ║ PRIMARY,category,id ║ PRIMARY ║       8 ║ characters1_.item_id ║    1 ║ Using where                                                         ║
  ║  1 ║ SIMPLE      ║ characters5_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
  ║  1 ║ SIMPLE      ║ characters7_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
  ║  1 ║ SIMPLE      ║ characters2_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
  ║  1 ║ SIMPLE      ║ characters3_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
  ║  1 ║ SIMPLE      ║ characters9_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
  ║  1 ║ SIMPLE      ║ characters8_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
  ║  1 ║ SIMPLE      ║ characters6_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
  ║  1 ║ SIMPLE      ║ characters4_  ║ ref    ║ FK_47,name,value    ║ FK_47   ║       9 ║ characters1_.item_id ║    9 ║ Using where; Distinct                                               ║
  ╚════╩═════════════╩═══════════════╩════════╩═════════════════════╩═════════╩═════════╩══════════════════════╩══════╩═════════════════════════════════════════════════════════════════════╝
  </code></pre>
  
  <h2>Some thoughts</h2>
  
  <p>I thought that this issue related to the <code>group by</code> and <code>sort by</code>. <strong>But</strong> same as in the <code>bit-wise</code> case, when <code>group by</code> and <code>sort by</code> is being removed from the query the run-time can still be above 6 seconds!</p>
  
  <p>Any ideas what is the cause for the lame performances?</p>
  
  <p>Thanks,
  Oak</p>
  
  <p>p.s</p>
  
  <p>note: this cannot be <code>cache issue</code> as the first run can be very fast and only few runslater the query will perform very badly.</p>