how to eliminate union for better performance in mysql

Question

Hi i want fetch the records depends on different condition i used union worked fine but taking more than 15 secs so how can we eliminate union or make the query faster

QUERY:

(SELECT p.professional_id,
        p.company_name,
        pbt.name AS professional_business_type_name,
        pbtm.kukun_url,
        p.kukun_score,
        cc.year_founded,
        p.contractor_category,
        p.permit_data_count,
        p.cost_range_code,
        fpcr.cost_min_value,
        fpcr.cost_max_value
 FROM   professional p
        INNER JOIN company_contact cc
                ON cc.company_contact_id = p.company_contact_id
        INNER JOIN professional_business_type_map AS pbtm
                ON pbtm.professional_id = p.professional_id
        INNER JOIN of_professional_business_type_organization AS pbt
                ON pbt.professional_business_type_organization_id =
                   pbtm.professional_business_type_organization_id
        INNER JOIN f_professional_cost_range fpcr
                ON fpcr.cost_range_code = p.cost_range_code
 WHERE  p.professional_id != 262100
        AND cc.company_city_id = 5229
        AND pbt.professional_business_type_organization_id = 2
        AND p.cost_range_code = 4
 ORDER  BY p.kukun_score DESC
 LIMIT  5)
UNION
(SELECT p.professional_id,
        p.company_name,
        pbt.name AS professional_business_type_name,
        pbtm.kukun_url,
        p.kukun_score,
        cc.year_founded,
        p.contractor_category,
        p.permit_data_count,
        p.cost_range_code,
        fpcr.cost_min_value,
        fpcr.cost_max_value
 FROM   professional p
        INNER JOIN company_contact cc
                ON cc.company_contact_id = p.company_contact_id
        INNER JOIN professional_business_type_map AS pbtm
                ON pbtm.professional_id = p.professional_id
        INNER JOIN of_professional_business_type_organization AS pbt
                ON pbt.professional_business_type_organization_id =
                   pbtm.professional_business_type_organization_id
        INNER JOIN f_professional_cost_range fpcr
                ON fpcr.cost_range_code = p.cost_range_code
 WHERE  p.professional_id != 262100
        AND cc.company_city_id = 5229
        AND pbt.professional_business_type_organization_id = 2
 ORDER  BY p.kukun_score DESC
 LIMIT  5)
UNION
(SELECT p.professional_id,
        p.company_name,
        pbt.name AS professional_business_type_name,
        pbtm.kukun_url,
        p.kukun_score,
        cc.year_founded,
        p.contractor_category,
        p.permit_data_count,
        p.cost_range_code,
        fpcr.cost_min_value,
        fpcr.cost_max_value
 FROM   professional p
        INNER JOIN company_contact cc
                ON cc.company_contact_id = p.company_contact_id
        INNER JOIN professional_business_type_map AS pbtm
                ON pbtm.professional_id = p.professional_id
        INNER JOIN of_professional_business_type_organization AS pbt
                ON pbt.professional_business_type_organization_id =
                   pbtm.professional_business_type_organization_id
        INNER JOIN f_professional_cost_range fpcr
                ON fpcr.cost_range_code = p.cost_range_code
 WHERE  p.professional_id != 262100
        AND cc.company_city_id = 5229
 ORDER  BY p.kukun_score DESC
 LIMIT  5)
UNION
(SELECT p.professional_id,
        p.company_name,
        pbt.name AS professional_business_type_name,
        pbtm.kukun_url,
        p.kukun_score,
        cc.year_founded,
        p.contractor_category,
        p.permit_data_count,
        p.cost_range_code,
        fpcr.cost_min_value,
        fpcr.cost_max_value
 FROM   professional p
        INNER JOIN company_contact cc
                ON cc.company_contact_id = p.company_contact_id
        INNER JOIN professional_business_type_map AS pbtm
                ON pbtm.professional_id = p.professional_id
        INNER JOIN of_professional_business_type_organization AS pbt
                ON pbt.professional_business_type_organization_id =
                   pbtm.professional_business_type_organization_id
        INNER JOIN f_professional_cost_range fpcr
                ON fpcr.cost_range_code = p.cost_range_code
 WHERE  p.professional_id != 262100
        AND cc.company_state_id = 5
 ORDER  BY p.kukun_score DESC
 LIMIT  5)
LIMIT 5; 

Answer 1

(Likely Bug) You need ORDER BY p.kukun_score DESC before the UNION's LIMIT 5. Today MySQL may sequentially perform all parts of the UNION, combine all of them, then do the LIMIT. In some future version, it is likely to, for example, perform the SELECTs in parallel, thereby jumbling the results.

Hence if you want the rows from the first SELECT to be delivered first, you must add a column and ORDER BY.

 ( SELECT 1 AS sequence, ... )
 UNION ALL
 ( SELECT 2 AS sequence, ... )
 ...
 ORDER BY sequence, kukun_score DESC
 LIMIT 5

Also, UNION is the same as UNION DISTINCT, which add a de-dup pass to the operation. That is, the semantics requires evaluating all the selects.

These INDEXes may help:

cc:  (company_state_id, company_contact_id, year_founded)
cc:  (company_city_id, company_contact_id, year_founded)
fpcr:  (cost_range_code, cost_max_value, cost_min_value)

Those indexes will be "covering" and optimal for the SQL you have.

Some benefit will come from moving fpcr out of the union. That is, first UNION all the other tables, then JOIN to fpcr. to get the two columns from it. This will speed things up because it it needs to reach into that table only 5 times, instead of thousands times (however many rows are in the 4 temporary tables).