在其他连接的 select 语句中优化联合连接 [英] optimizing a union join inside select statement of other joins
问题描述
我有一个由 3 -4 部分构建的查询.一旦我添加了带有 join 的 union 连接，这需要超过 140 秒才能运行.如何更改联合连接以更快地执行它.
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`
名为testing 的表别名需要很长时间才能执行.然后我需要把它变成一个视图
The table alias called testing is the one taking long to execute. I need to then turn this into a view
原始查询没有加入 Sugarcrm.
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`
推荐答案
您的 LEFT JOIN 从 Sugarcrm.contacts 到 Sugarcrm.email_addr_bean_rel在 id=bean_id 上没问题，但是您对 Primary_Address = 1 的测试OR(主地址不是 NULL AND primary_address <> 0 )是浪费.
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.
非空意味着它有一个值.1 的第一个限定符没问题，但随后您测试任何不等于 0 的地址(因此 1 是，但 2、3、400、1809 或任何其他号码.那么为什么不直接采用我的简化方式呢.
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
我不介意帮忙，但从现在开始，你应该看看我在做什么...建立关系...从你的数据(订单)的基础开始，看看 ONE PATH关于如何连接到您的联系人"表...编写这些连接(作为左连接).然后，将您的路径写入 SUGAR 帐户联系人并写入 THOSE 连接(也是左连接).不要尝试预查询所有可能的联系人，而是使用 CASE/WHEN 来确定基于空路由获取哪个而不是像我与联系人、客户、公司等一样.您将从一个路径获取数据与另一个...保持一致.
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.
这篇关于在其他连接的 select 语句中优化联合连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
