there was an index on idProduct and idFilter. (primary). Removed it, changed it to yours... no speed change
select[...]
Showing rows 0 - 18 ( 19 total, Query took 2.9600 sec)
EXPLAIN SELECT f.id, f.name, f.type, count( pf.idProduct ) AS cnt
FROM _filters f
LEFT JOIN productFilters pf ON pf.idFilter = f.id
WHERE 1
AND idProduct
IN (
SELECT idProduct
FROM productFilters
WHERE idFilter =77
AND idProduct
IN (
SELECT idProduct
FROM productFilters
WHERE idFilter =76
AND idProduct
IN (
SELECT idProduct
FROM productFilters
WHERE idFilter =16
AND idProduct
IN (
SELECT idProduct
FROM productFilters
WHERE idFilter =11
)
)
)
)
GROUP BY f.id
ORDER BY f.name ASC
[ Edit ] [ Skip Explain SQL ] [ Create PHP Code ]
+ Options
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY f ALL PRIMARY NULL NULL NULL 115 Using temporary; Using filesort
1 PRIMARY a ref filter filter 4 database.f.id 4791 Using where
2 DEPENDENT SUBQUERY productFilters index_subquery filter,index_on_ids index_on_ids 8 func,const 1 Using index; Using where
3 DEPENDENT SUBQUERY productFilters index_subquery filter,index_on_ids index_on_ids 8 func,const 1 Using index; Using where
4 DEPENDENT SUBQUERY productFilters index_subquery filter,index_on_ids index_on_ids 8 func,const 1 Using index; Using where
5 DEPENDENT SUBQUERY productFilters index_subquery filter,index_on_ids index_on_ids 8 func,const 1 Using index; Using where
describe productFilters
Full texts Field Type Null Key Default Extra
Edit Edit Edit Inline Edit Copy Copy Delete Delete idProduct int(11) NO MUL NULL
Edit Edit Edit Inline Edit Copy Copy Delete Delete idFilter int(11) NO MUL NULL
show index from productFilters
productFilters 1 filter 1 idFilter A 115 NULL NULL BTREE
productFilters 1 index_on_ids 1 idProduct A 68871 NULL NULL BTREE
productFilters 1 index_on_ids 2 idFilter A 550971 NULL NULL BTREE