current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

I have a query I would like to optimize, It takes 29 seconds to complete. I have tried creating a view of the union join but gives worse result.

 select o.orderno, o.orderdate, c.lastname 
from orders o 
join (select 
    `hdb`.`contacts`.`CONTACTID` AS `CONTACTID`, 
    `hdb`.`contacts`.`LASTNAME` AS `LASTNAME`
    from `hdb`.`contacts` 
    union 
select 
    `sugarcrm`.`contacts`.`id` AS `CONTACTID`,
    `sugarcrm`.`contacts`.`last_name` AS `LASTNAME`
    from `sugarcrm`.`contacts` ) c on c.CONTACTID = o.CONTACTID  


order by orderno desc
limit 0,100000000

Under the mysql slow query log i get the following part:

select 
        `hdb`.`contacts`.`CONTACTID` AS `CONTACTID`, 
        `hdb`.`contacts`.`CLIENTID` AS `CLIENTID`, 
         concat(`hdb`.`contacts`.`FIRSTNAME`,_utf8' ',coalesce(`hdb`.`contacts`.`INITIALS`,_utf8'')) AS `FIRSTNAME`, 
        `hdb`.`contacts`.`LASTNAME` AS `LASTNAME`, 
        `hdb`.`contacts`.`PHONE` AS `PHONE`, 
        `hdb`.`contacts`.`FAX` AS `FAX`, 
        `hdb`.`contacts`.`DEPARTMENT` AS `DEPARTMENT`, 
        `hdb`.`contacts`.`TITLE` AS `TITLE`, 
        `hdb`.`contacts`.`INFO` AS `INFO`, 
        `hdb`.`contacts`.`SALUTATION` AS `SALUTATION`, 
        `hdb`.`contacts`.`EMAIL` AS `EMAIL`,    
        CASE 
            WHEN `hdb`.`contacts`.`ACTIVE` != 0 THEN 0
            ELSE 1
        END DELETED,        
        'paradox' AS `SOURCEDATABASE`
        from `hdb`.`contacts` 
        union 
    select 
        `sugarcrm`.`contacts`.`id` AS `CONTACTID`,
        `sugarcrm`.`accounts_contacts`.`account_id` AS `CLIENTID`,
        `sugarcrm`.`contacts`.`first_name` AS `FIRSTNAME`,
        `sugarcrm`.`contacts`.`last_name` AS `LASTNAME`,
        `sugarcrm`.`contacts`.`phone_work` AS `PHONE`,
        `sugarcrm`.`contacts`.`phone_fax` AS `FAX`,
        `sugarcrm`.`contacts`.`department` AS `department`,
        `sugarcrm`.`contacts`.`title` AS `title`,
        `sugarcrm`.`contacts`.`description` AS `INFO`,
        `sugarcrm`.`contacts`.`salutation` AS `salutation`,
        `sugarcrm`.`email_addresses`.`email_address` AS `Email`,            
        `sugarcrm`.`contacts`.`deleted` AS DELETED,
        'sugar' AS `SOURCEDATABASE` 
        from (((`sugarcrm`.`contacts` 
        left join `sugarcrm`.`email_addr_bean_rel` 
        on((`sugarcrm`.`contacts`.`id` = `sugarcrm`.`email_addr_bean_rel`.`bean_id`))) 
        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`)))

But this runs quickly if run on its own. Using explain gives me the following.

1, PRIMARY, o, ALL, , , , , 6085, Using temporary; Using filesort
1, PRIMARY, <derived2>, ALL, , , , , 18877, Using where
2, DERIVED, contacts, ALL, , , , , 8009, 
3, UNION, contacts, ALL, , , , , 10251, 
, UNION RESULT, <union2,3>, ALL, , , , , ,
share|improve this question
    
I'm quite sure 90% of the queries you've posted are irrelevant tinge question. Please edit your queries given to the absolute minimum code that demonstrates the problem. See SSCCE, preferably on SQLFiddle so we can code against something. –  Bohemian♦ Jun 11 '13 at 9:47

1 Answer 1

up vote 1 down vote accepted

For your top query, I would alter it to a double left-join to each respective table of contacts... That said, it appears a customer's contact ID should be in EITHER of them (unless you have other contact tables). That said, the left-join will be quicker than doing a prequery / join. For the name, I would case/when... if the one table doesn't have a match (via IS NULL), then use the name from the other table and vice-versa... if it DOES have a value, use it. Then, just make sure your order table has an index on the order number.

select 
      o.orderno, 
      o.orderdate, 
      CASE when hc.contactid is null 
           then sc.last_name else hc.lastname end as LastName
   from 
      orders o 
         LEFT JOIN hdb.contacts hc
            ON o.ContactID = hc.ContactID
         LEFT JOIN sugarcrm.contacts sc
            ON o.ContactID = sc.id
   order by 
      orderno desc
   limit 0, 100000000

If you DO have other tables of contacts, but only care about those associated with the "hc" and "sc" aliased tables, I would add a WHERE clause for

WHERE hc.contactID IS NOT NULL
   OR sc.id IS NOT NULL

to ensure at least ONE of the tables DOES have a match.

share|improve this answer
    
the two left joins is same as doing union? –  bonez Jun 12 '13 at 10:53
    
can you help me with this one [stackoverflow.com/questions/17062994/… –  bonez Jun 12 '13 at 10:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.