I have a question about organizing my web application. I have many tables. I must search in it by different parameters. for example: country: 2 and category1: 33 and language: 3.
select distinct s.id from tbl_item s
left join tbl_item_act ep on s.id = ep.item_id
left join tbl_item_act_category cl3 on cl3.id = ep.category_id
left join tbl_item_act_category cl2 on cl2.id = cl3.parent_id
left join tbl_item_act_category cl1 on cl1.id = cl2.parent_id
left join tbl_city ci on ci.id = s.city_id
left join tbl_country co on co.id = ci.country_id
left join tbl_language la on la.id = cl2.language_id
left join tbl_item_subject sbb on sbb.item_id = s.id
left join tbl_subject sb on sb.id = sbb.subject_id and sb.subject_type = 'subject'
left join tbl_subject ev on ev.id = sbb.subject_id and ev.subject_type = 'event'
left join tbl_season se on se.id = cl1.season_id or se.id = cl2.season_id
left join tbl_some_type et on et.id = s.education_type_id
left join tbl_item_tag_binding stagb on stagb.item_id = s.id
left join tbl_item_tag stag on stag.id = stagb.tag_id
left join tbl_item_type_binding stb on stb.item_id = s.id
left join tbl_item_type st on st.id = stb.item_type_id
where s.original_id is null and cl1.id = 33 and la.id = 2 and se.id = 2
I must execute 9-10 queries on one page. I takes about 50ms each query. I tried to generate one table (such as view) and store joined data here (as cache table) but it stores millions rows and searching takes about 500ms each query. I tried nosql (mongodb) search (big data tree) and it takes a little time - about 20-30 ms but it is much to me.
What the best practices to create fast search by many parameters in my data structure?
sorry for my english.