[eluser]Muser[/eluser]
Hi
I'm building a social networking site that may have a lot of traffic and I would like to expose to you if you think I'm on the right line.
I have complicated queries to do in search lists, with several filters on it. An example of these querys is this:
Code:
select `offers`.`id` AS `id`,
`loc_zip_codes`.`ZIP_CODE` AS `zip_code`,
`offers`.`sector_id` AS `sector_id`,
`loc_zip_codes`.`id_province` AS `id_prov`,
`loc_provinces`.`auton_region_id` AS `id_region`,
`offers`.`title` AS `title`,
`offers`.`description` AS `description`,
`offers`.`date_published` AS `date_published`,
`offers`.`date_end` AS `date_end`,
`offers`.`created_at` AS `created_at`,
`offers`.`updated_at` AS `updated_at`,
`companies`.`name` AS `company_name`,
(case when (`companies`.`scope` = _utf8'myCity') then `loc_zip_codes`.`name_es`
when (`companies`.`scope` = _utf8'myProvince') then `loc_provinces`.`name_es`
when (`companies`.`scope` = _utf8'myRegion') then `loc_autonomous_regions`.`name_region_es`
when (`companies`.`scope` = _utf8'myCountry') then _utf8'España' end) AS `scope`,
`companies`.`id` AS `company_id`,
`sectors`.`name_ca` AS `sector`,
`offers`.`url_link` AS `url_link`
from (((((`offers`
left join `companies` on((`offers`.`company_id` = `companies`.`id`)))
left join `loc_zip_codes` on((`companies`.`idMunicipio` = `loc_zip_codes`.`idMunicipio`)))
left join `loc_provinces` on((`loc_zip_codes`.`id_province` = `loc_provinces`.`id`)))
left join `loc_autonomous_regions` on((`loc_provinces`.`auton_region_id` = `loc_autonomous_regions`.`id`)))
left join `sectors` on((`offers`.`sector_id` = `sectors`.`id`)))
To simplify my code, I have stored this sql query in a mysql view and pointed a datamapper model to the view. So, the search filtering becomes querying the mysql view.
Do you think that using this kind of mysql views I'll get a slow performance?
How I could simplify this query?
