Slow Query With Multiple Left Outer Joins

Question

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;

Answer 1

Are there ever two billing addresses for one account? If not, toss the LEFT JOIN for that address; it has no affect on the COUNT(*). Ditto for all the other LEFT JOINs.

Even if one of the LEFT JOINs leads to extra rows, get rid of them because of the DISTINCT.

Are there multiple profiles for each user? If not, then much of the other baggage is wasted.

Then get rid of the nested ("derived") SELECT.

SELECT COUNT(*)   -- or maybe COUNT(DISTINCT profile_id)
    FROM profile AS p
    WHERE P.ORGANIZATION_ID = 1
      AND P.ISANONYMOUS = false 

That should give you the same count as your original query. If not, please explain where there are 1:many mappings that are not obvious.

If you aren't really asking about COUNT(*), but rather fetching multiple columns, then show us the real query; it may need other optimizations. Also, provide SHOW CREATE TABLE so we don't waste time recommending indexes you may already have.