performance - Mysql Filter matches based on previously selected filter values? -

i'm seeing type of filter everywhere, know magento have same principle.

so, deal. easy , solution filter strict value , exclude filter options dont have same value. i'm trying do, this web site , tons of filter value allow selecting multiple values within same attribute. it's maches (i know how filter products selected filter, not it, matches)

most logicaly solution eav, many joins make slower query, cron on time "one-happy-table" , , table structure :

create table if not exists `filter` (   `productid` int(10) unsigned not null,   `attributeid` int(10) unsigned not null,   `valueid` int(10) unsigned not null,   unique key `productid_attributeid_valueid` (`productid`,`attributeid`,`valueid`),   key `productid` (`productid`),   key `valueid` (`valueid`),   key `attributeid` (`attributeid`) ) engine=memory default charset=utf8 row_format=compact; 

and here query i'm using accomplish that. query give me results need, slow.

        select  sql_no_cache          f.attributeid,              f.valueid,         (select count(productid)          filter fp         f.valueid = fp.valueid               , (productid in (select productid                                   filter                                                                 valueid in ( '33' ))               or attributeid = (select  attributeid                                   filter                                   valueid in ( '33' ) limit 1))           )  items         filter f         group f.attributeid,f.valueid  

when filter table have more 5000 records, slower , slower. tried different indexes, table storage engines. when add 5000 products, , every product have, example 7 filter-values, query takes 16 seconds. least query time made 5.5 seconds, need faster that...

does have idea make things faster? want accomplish speed here! :)