Making the query for more efficient

What I want is the Participants (people, with role as participant) of one event, combined with people of the same companies as that of participants, but whose role is manager.

A Person's role is stores in person_role_membership, but a person's company is stored in People. I have crafted the following query, which I think does the job pretty well. Please ignore the extra joins and information. I am fetching all the participants, then UNION-ing with all the managers, for which I am once again fetching all the participants. Now the trouble is this whole query, for a small subset is taking 9 seconds. Is there a way to make it faster?

select distinct * 
from 
(
SELECT 
 p.id as p_id, p.first_name as p_first_name, p.last_name as p_last_name,p_r.name as p_role, 
 p.job_title as p_job_title, 
 p_d.email as p_email, p_d.phone_1 as p_phone, p_d.phone_ext_1 as p_ext,
 c.name as p_company, p_c.name as p_parent_company

 FROM person_role_memberships as prm

 left join people as p on prm.person_id = p.id and prm.person_role_id between 32 and 35
 left join person_roles as p_r on p_r.id = prm.person_role_id
 left join person_details as p_d on p.id = p_d.person_id and p_d.type = 'BusinessDetail'
 left join companies as c on c.id = p.company_id
 left join companies as p_c on p_c.id = p.parent_company_id

 where 
 p.id is not null 

) as parts
union (

select p.id, p.first_name,p.last_name,prm.person_role_id, p.job_title,  'cp email', 'phone','ext',c.name, d.name

from people as p -- All those people

left join person_role_memberships as prm on prm.person_id = p.id -- whose roles are like this
left join companies as c on p.company_id = c.id
left join companies as d on c.parent_id = d.id
-- and whose companies are like those people whose roles are like this

where company_id = any
(
select company_id from people as p
left join person_role_memberships as prm on prm.person_id = p.id
where prm.person_role_id between 32 and 35-- and other conditions;
)

and (person_role_id = 14 or person_role_id = 15))

Answers


My guess would be that the 'any' part of your query is slowing things down. By the looks of it I think you don't even need it because you could filter on person_role_memberships directly:

select p.id, p.first_name,p.last_name,prm.person_role_id, p.job_title,  'cp email', 'phone','ext',c.name, d.name

from people as p -- All those people
    left join person_role_memberships as prm on prm.person_id = p.id -- whose roles are like this
    left join companies as c on p.company_id = c.id
    left join companies as d on c.parent_id = d.id

-- and whose companies are like those people whose roles are like this
where prm.person_role_id between 32 and 35 -- and other conditions;
    and (person_role_id = 14 or person_role_id = 15))

The 'any'-statement is causing the database to look through the entire PEOPLE table for every record.


Need Your Help

Plotting random numbers in Python

python random plot numbers

I'm trying to generate and plot random numbers using: