The main problem is the speed of this query...it is running absurdly slow.

Is there a way to speed it up? These 3 fields I'm selecting distinct on take a lifetime to run. Also, the join is going to expand to two more tables as well.

Anyone have any suggestions? It works, as in it runs without erros, but 5 minutes of running, I had to abort several times :/

SELECT DISTINCT
ON(a.lead_id, a.activity_type_id, d.attribute_name)



FROM
reporting.os_marketing_activities a
INNER JOIN reporting.os_marketing_leads b ON b.id = a.lead_id
INNER JOIN reporting.os_marketing_activity_mapping c ON c.activity_type_id = a.activity_type_id
LEFT JOIN reporting.os_marketing_activity_attributes d ON d.id = a.id

first thing you should do is run explain plan. indexes may help. if you still have problems, post here with the explain plan.

how long does it take without distinct? what are the row counts for distinct vs non distinct?

you may be able to play around with subselects with the distinct keyword since only two of the tables have the dupes, but we'll get to that later.

This post was edited by carteblanche on Apr 22 2015 06:11pm

Without the distinct portion, it runs rather quickly. With the distinct, I ran it for minutes and it didn't work. I think I literally let it run for 5 minutes.

i'm waiting on that explain plan. with and without distinct.