Why does this "Mysql" query hangs once a while?

Question

I'm working with complex MySQL query to fetch data over Key,Value table. IN order to achieve that we are doing multiply self joins. At the beginning we thought that the issue is bit-wise operations in MySQL as they cannot be done with indexes. But The issue still exists without using bit-wise operations. i.e the same happens if changing all bit-wise OR to equal (==)

Query

The following query usually runs around 500 Milliseconds . But once a while MySQL runs this query over 10 seconds!! and even more..

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<>0
   OR characters2_.value=0)
AND characters2_.name="somevalue2"
AND (characters3_.value & 2<>0
   OR characters3_.value=0)
AND characters3_.name="somevalue3"
AND (characters4_.value = 128<>0
   OR characters4_.value=0)
AND characters4_.name="somevalue4"
AND (characters5_.value = 1<>0
   OR characters5_.value=0)
AND characters5_.name="somevalue5"
AND (characters6_.value = 16392<>0
   OR characters6_.value=0)
AND characters6_.name="somevalue6"
AND (characters7_.value = 1<>0     OR characters7_.value=0)
AND characters7_.name="somevalue7"
AND (characters8_.value = 256<>0     OR characters8_.value=0)
AND characters8_.name="somevalue8"
AND (characters9_.value = 124<>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

Mysql explain

After using explain we get

╔════╦═════════════╦═══════════════╦════════╦═════════════════════╦═════════╦═════════╦══════════════════════╦══════╦═════════════════════════════════════════════════════════════════════╗
║ 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                                               ║
╚════╩═════════════╩═══════════════╩════════╩═════════════════════╩═════════╩═════════╩══════════════════════╩══════╩═════════════════════════════════════════════════════════════════════╝

Some thoughts

I thought that this issue related to the group by and sort by. But same as in the bit-wise case, when group by and sort by is being removed from the query the run-time can still be above 6 seconds!

Any ideas what is the cause for the lame performances?

Thanks, Oak

p.s

note: this cannot be cache issue as the first run can be very fast and only few runslater the query will perform very badly.

Answer 1

Ok, it took a while to figure this out. It seems that because of some duplicates keys the query were real slow. i.e if one have table

id, key ,value

and the key cannot appear twice for the same id. then make sure you have unique on id,key i.e

ALTER TABLE key_value_table ADD UNIQUE( key, id);

the advantage of this can be seen (and in time of course) explain query: the range is no longer been used and now MySQL do ref for all the select sentences