optimizing a union join inside select statement of

I have a query I built in 3 -4 parts. This takes over 140secs to run once I add the union join with join. How can I change the union join to execute it faster.

SELECT
    testing.CLIENTID,
    testing.COMPANY,
    testing.CONTACT,
    testing.CONTACTID,
    `orders`.`ORDERNO` AS `ORDERNO`,
    `orders`.`BIDNO` AS `BIDNO`,
    `projects`.`PROJID` AS `PROJID`,
    `projects`.`PROJCODE` AS `PROJCODE`,
    `projects`.`StartDate` AS `StartDate`,
    `category`.`type` AS `CATEGORY`,
    `projects`.`country` AS `COUNTRY`,
    `projects`.`VALUE` AS `VALUE`,
    `projects`.`PROCESSOR` AS `PROCESSOR`,
    `projects`.`NES` AS `NES`,
    `projects`.`SPECSALE` AS `SPECSALE`,
    `projects`.`OFFICE` AS `OFFICE`,
    `projects`.`LORM` AS `LORM`,
    `lookupcountry`.`REGION` AS `REGION`
FROM
    (
        (
            (
                (
                    (
                        (
                            SELECT
                                contactmerge.CLIENTID,
                                contactmerge.CONTACT,
                                contactmerge.CONTACTID,
                                accountmerge.COMPANY
                            FROM
                                (
                                    SELECT
                                        `hdb`.`contacts`.`CONTACTID` AS `CONTACTID`,
                                        `hdb`.`contacts`.`CLIENTID` AS `CLIENTID`,
                                        concat(
                                            `hdb`.`contacts`.`FIRSTNAME`,
                                            " ",
                                            `hdb`.`contacts`.`LASTNAME`
                                        ) AS CONTACT,
                                        _utf8 'paradox' AS `SOURCEDATABASE`
                                    FROM
                                        `hdb`.`contacts`
                                    UNION
                                        SELECT
                                            `sugarcrm`.`contacts`.`id` AS `CONTACTID`,
                                            `sugarcrm`.`accounts_contacts`.`account_id` AS `CLIENTID`,
                                            concat(
                                                `sugarcrm`.`contacts`.`first_name`,
                                                " ",
                                                `sugarcrm`.`contacts`.`last_name`
                                            ) AS CONTACT,
                                            _utf8 'sugar' AS `SOURCEDATABASE`
                                        FROM
                                            (
                                                (
                                                    (
                                                        (
                                                            `sugarcrm`.`contacts`
                                                            LEFT JOIN `sugarcrm`.`email_addr_bean_rel` ON (
                                                                (
                                                                    (
                                                                        `sugarcrm`.`contacts`.`id` = `sugarcrm`.`email_addr_bean_rel`.`bean_id`
                                                                    )
                                                                    AND (
                                                                        (
                                                                            `sugarcrm`.`email_addr_bean_rel`.`primary_address` = 1
                                                                        )
                                                                        OR (
                                                                            (
                                                                                `sugarcrm`.`email_addr_bean_rel`.`primary_address` IS NOT NULL
                                                                            )
                                                                            AND (
                                                                                `sugarcrm`.`email_addr_bean_rel`.`primary_address` <> 0
                                                                            )
                                                                        )
                                                                    )
                                                                )
                                                            )
                                                        )
                                                        LEFT JOIN `sugarcrm`.`accounts_contacts` ON (
                                                            (
                                                                `sugarcrm`.`contacts`.`id` = `sugarcrm`.`accounts_contacts`.`contact_id`
                                                            )
                                                        )
                                                    )
                                                    JOIN `sugarcrm`.`email_addresses` ON (
                                                        (
                                                            `sugarcrm`.`email_addr_bean_rel`.`email_address_id` = `sugarcrm`.`email_addresses`.`id`
                                                        )
                                                    )
                                                )
                                                LEFT JOIN `sugarcrm`.`accounts` ON (
                                                    (
                                                        `sugarcrm`.`accounts`.`id` = `sugarcrm`.`accounts_contacts`.`account_id`
                                                    )
                                                )
                                            )
                                ) AS contactmerge
                            LEFT JOIN (
                                SELECT
                                    CLIENTID,
                                    `hdb`.`clients`.`COMPANY` AS `COMPANY`
                                FROM
                                    `hdb`.`clients`
                                UNION
                                    SELECT
                                        id AS CLIENTID,
                                        `sugarcrm`.`accounts`.`name` AS `COMPANY`
                                    FROM
                                        `sugarcrm`.`accounts`
                            ) AS accountmerge ON contactmerge.CLIENTID = accountmerge.CLIENTID
                        ) AS testing
                    )
                    JOIN `orders` ON (
                        (
                            `testing`.`CONTACTID` = `orders`.`CONTACTID`
                        )
                    )
                )
                JOIN `projects` ON (
                    (
                        `orders`.`ORDERNO` = `projects`.`ORDERNO`
                    )
                )
            )
            JOIN `category` ON (
                (
                    `category`.`category_id` = `projects`.`category_id`
                )
            )
        )
        LEFT JOIN `lookupcountry` ON (
            (
                CONVERT (
                    `lookupcountry`.`COUNTRY` USING utf8
                ) = CONVERT (
                    `projects`.`country` USING utf8
                )
            )
        )
    )
ORDER BY
    `testing`.`COMPANY`,
    `projects`.`StartDate`

The table alias called testing is the one taking long to execute. I need to then turn this into a view

Original query without the joining of sugarcrm.

SELECT
    `clients`.`CORPORATE` AS `CORPORATE`,
    `clients`.`COMPANY` AS `COMPANY`,
    `clients`.`CLIENTID` AS `CLIENTID`,
    `contacts`.`CONTACTID` AS `CONTACTID`,
    concat(
            `contacts`.`LASTNAME`,
            `contacts`.`FIRSTNAME`,     
            `contacts`.`INITIALS`
    ) AS `Contact`,
    `orders`.`ORDERNO` AS `ORDERNO`,
    `orders`.`BIDNO` AS `BIDNO`,
    `projects`.`PROJID` AS `PROJID`,
    `projects`.`PROJCODE` AS `PROJCODE`,
    `projects`.`StartDate` AS `StartDate`,
    `category`.`type` AS `CATEGORY`,
    `projects`.`country` AS `COUNTRY`,
    `projects`.`VALUE` AS `VALUE`,
    `projects`.`PROCESSOR` AS `PROCESSOR`,
    `projects`.`NES` AS `NES`,
    `projects`.`SPECSALE` AS `SPECSALE`,
    `projects`.`OFFICE` AS `OFFICE`,
    `projects`.`LORM` AS `LORM`,
    `lookupcountry`.`REGION` AS `REGION`
FROM
    (
        (
            (
                (
                    (
                        `clients`
                        JOIN `contacts` ON (
                            (
                                `clients`.`CLIENTID` = `contacts`.`CLIENTID`
                            )
                        )
                    )
                    JOIN `orders` ON (
                        (
                            `contacts`.`CONTACTID` = `orders`.`CONTACTID`
                        )
                    )
                )
                JOIN `projects` ON (
                    (
                        `orders`.`ORDERNO` = `projects`.`ORDERNO`
                    )
                )
            )
            JOIN `category` ON (
                (
                    `category`.`category_id` = `projects`.`category_id`
                )
            )
        )
        LEFT JOIN `lookupcountry` ON (
            (
                CONVERT (
                    `lookupcountry`.`COUNTRY` USING utf8
                ) = CONVERT (
                    `projects`.`country` USING utf8
                )
            )
        )
    )
ORDER BY
    `clients`.`CORPORATE`,
    `clients`.`COMPANY`,
    `contacts`.`LASTNAME`,
    `projects`.`StartDate`

Your LEFT JOIN from sugarcrm.contacts to sugarcrm.email_addr_bean_rel ON the id=bean_id is ok, but then your test for Primary_Address = 1 OR ( primary address IS NOT NULL AND primary_address <> 0 ) is wasteful.

Not null mean it has a value. The first qualifier of 1 is ok, but then you test for any address not equal to 0 (thus 1 is, but so is 2, 3, 400, 1809 or any other number. So why not just take how I've simplified it.

SELECT
      O.ORDERNO,
      O.BIDNO,

      CASE when c.ContactID IS NULL
         then sc.id
         ELSE c.contactid  END as ContactID,

      CASE when c.ContactID IS NULL
         then sac.account_id
         ELSE c.clientid   END as ClientID,

      CASE when c.ContactID IS NULL
         then concat( sc.first_name, " ", sc.last_name ) 
         ELSE concat( c.FIRSTNAME, " ", c.LASTNAME )  END as Contact,

      CASE when c.ContactID IS NULL
         then sCli.`name`
         ELSE cCli.Company  END as Company,

      CASE when c.ContactID IS NULL
         then _utf8 'sugar' 
         ELSE _utf8 'paradox'  END as SOURCEDATABASE,

      P.PROJID,
      P.PROJCODE,
      P.StartDate,
      Cat.`type` AS CATEGORY,
      P.`country` AS COUNTRY,
      P.`VALUE` AS `VALUE`,
      P.PROCESSOR,
      P.NES,
      P.SPECSALE,
      P.OFFICE,
      P.LORM,
      LC.REGION
   FROM 
      orders O
         JOIN projects P
            ON O.ORDERNO = P.ORDERNO
            JOIN category Cat 
               ON P.category_id = Cat.category_id
            LEFT JOIN lookupcountry LC
               ON CONVERT( P.`country` USING utf8 ) = CONVERT( LC.COUNTRY USING utf8 )

         LEFT JOIN hdb.contacts c
            ON  O.ContactID = c.ClientID
            LEFT JOIN hdb.clients cCli
               ON c.ClientID = cCli.ClientID

         LEFT JOIN sugarcrm.contacts sc
            ON O.ContactID = sc.id
            LEFT JOIN sugarcrm.accounts sCli
               ON sc.id = sCli.id
            LEFT JOIN sugarcrm.accounts_contacts sac
               ON sc.id = sac.contact_id
               LEFT JOIN sugarcrm.accounts Acc 
                  ON sac.account_id = Acc.id

            LEFT JOIN sugarcrm.email_addr_bean_rel EABR
               ON sc.id = EABR.bean_id
              AND EABR.primary_address IS NOT NULL
              LEFT JOIN sugarcrm.email_addresses EA 
                 ON EABR.email_address_id = EA.id
ORDER BY
   CASE when c.ContactID IS NULL
      then sCli.`name`
      ELSE cCli.Company  END,
   P.StartDate

I don't mind helping, but from now on, you should take a look at what I'm doing... Establish the relationships... Start with the basis of your data (orders) and look at ONE PATH on how to connect to your "contacts" table... Write those joins (as left-joins). THEN, write your paths to the SUGAR account contacts and write THOSE joins (also left-joins). Don't try to prequery all possible contacts, but using the CASE/WHEN to determine which to get based on a null route vs not just as I have with the contact, client, company, etc. You will get the data from one path vs the other... just keep it consistent.