views:

52

answers:

1

I have a task to optimize this query, any idea will be appreciated.

SELECT DISTINCT `br`.`id` AS `branch_id`
       , `br`.`store_id`
       , `br`.`longitude`
       , `br`.`latitude`
       , `br`.`town_id`
       , `br`.`post_region`
       , `cb`.`cat0_id`
       , `cb`.`cat1_id`
       , `cb`.`cat2_id`
       , `plan`.`listing_plan_id`
       , `cat0`.`cat_topid`
       , `cg`.`tarrif` 
FROM   `mvcl_local_new`.`branch` AS `br`
       INNER JOIN `mvcl_local_new`.`voucher_branch` AS `vb` ON br.id = vb.branch_id
       INNER JOIN `mvcl_local_new`.`voucher` AS `v` ON v.id = vb.voucher_id
       INNER JOIN `category_voucher` AS `cb` ON cb.voucher_id = v.id
       INNER JOIN `category_voucher_listing_plans` AS `plan` ON plan.voucher_id = v.id
       INNER JOIN `category_level0` AS `cat0` ON cb.cat0_id = cat0.id
       LEFT JOIN `category_tariffs` AS `cg` ON cg.voucher_id = v.id 
WHERE  (
        br.latitude != 0 
        and br.longitude != 0 
        and br.post_region <> ''
        and valid_from < 1286876423 
        and expires > 1286876423
        and 
        (
            (
                plan.listing_plan_id IN (1,2,3) 
                and cg.town_id = br.town_id 
                and cg.is_active = 1 
                and cg.status_code='Active' 
                and cg.tarrif_id=0
            )
            or plan.listing_plan_id=4
        )
    );
+2  A: 

For optimization queries is very convenient to use MySQL Profiler in dbForge Studio For MySQL.

Zoitc2014