gpt4 book ai didi

mysql - 优化其他连接的 select 语句内的 union join

转载 作者:行者123 更新时间:2023-11-29 08:29:04 25 4
gpt4 key购买 nike

我有一个由 3 -4 部分组成的查询。一旦我使用 join 添加 union join,这需要超过 140 秒才能运行。如何更改联合连接以更快地执行它。

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 的表别名需要很长时间才能执行。然后我需要将其转换为 View

原始查询,未加入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`

最佳答案

您从 Sugarcrm.contacts 到 Sugarcrm.email_addr_bean_rel 的 LEFT JOINON id=bean_id 没问题,但是你对 Primary_Address = 1 的测试OR(主地址 IS NOT NULL AND Primary_address <> 0)是浪费。

不为空意味着它有一个值。第一个限定符 1 是可以的,但是接下来您测试任何不等于 0 的地址(因此 1 是,但 2、3、400、1809 或任何其他数字。那么为什么不直接采用我的简化方法呢。

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

我不介意提供帮助,但从现在开始,你应该看看我在做什么...建立关系...从数据(订单)的基础开始,看看一条路径关于如何连接到“联系人”表...编写这些连接(作为左连接)。然后,写入 SUGAR 帐户联系人的路径并写入这些连接(也是左连接)。不要尝试预先查询所有可能的联系人,而是使用 CASE/WHEN 来确定基于空路由获取哪个联系人,而不是像我对联系人、客户、公司等那样。您将从一个路径获取数据与其他...保持一致即可。

关于mysql - 优化其他连接的 select 语句内的 union join,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17152866/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com