I am using the following query which has multiple left joins. it takes around 3.2s for execution. I have added indexes on every column which is taking part in JOIN. is it something regarding DB performance? What all columns I need to consider for index. is a composite index needed in this case?
SELECT
COUNT(*) FROM
(SELECT DISTINCT P.PROFILE_ID
FROM
user.PROFILE P
LEFT OUTER JOIN user.PROFILE_ACCOUNT P_AC ON P_AC.PROFILE_ID = P.PROFILE_ID
LEFT OUTER JOIN user.ACCOUNT AC ON AC.ACCOUNT_ID = P_AC.ACCOUNT_ID
LEFT OUTER JOIN user.PROFILE_ADDRESS SA ON SA.ADDRESS_ID = P.SHIPPING_ADDRESS_ID
LEFT OUTER JOIN user.PROFILE_ADDRESS BA ON BA.ADDRESS_ID = P.BILLING_ADDRESS_ID
LEFT OUTER JOIN user.PROFILE_ADDRESS AC_BA ON AC_BA.ADDRESS_ID = AC.BILLING_ADDRESS_ID
LEFT OUTER JOIN user.PROFILE_ADDRESS AC_SA ON AC_SA.ADDRESS_ID = AC.SHIPPING_ADDRESS_ID
LEFT OUTER JOIN user.PROFILE_CUSTOM_PROPS PCP ON PCP.PROFILE_ID = P.PROFILE_ID
WHERE P.ORGANIZATION_ID = 1
AND P.ISANONYMOUS = false
) AS T_PROF_ID;
EXPLAIN
plan, but I suspect theDISTINCT
keyword is your major issue here.