gpt4 book ai didi

mysql - 如何优化包含连接和子查询的 sql 联合

转载 作者:行者123 更新时间:2023-11-30 01:37:13 25 4
gpt4 key购买 nike

我查看了很多有关sql优化的评论,但找不到答案。

我的应用程序使用此 SQL ...

SELECT * FROM ((
SELECT DISTINCT
c.companyname,
c.tradingas,
a.quarantined,
c.companyid,
a.address1,
a.city,
a.postcode,
a.region,
c.origin_dsn,
d.title,
d.firstname,
d.surname,
d.position,
a.telephoneno,
a.companyemail,
addresstypes,
markets
FROM companies c
join addresses a on c.companyid = a.companyid
join contacts d on c.companyid = d.companyid
where d.origin_dsn = 'ifd'
and primarycontact = 1
and d.clientid = 0
and c.origin_dsn = 'ifd' and
a.origin_dsn = 'ifd' and
c.companyid in (
select distinct companyid
from products
where description IN ('Windows and Doors','Vertical Sliders','Bi-Fold Doors')
AND type IN ('wd:f','wd:b')
AND material = 'PVCu'
AND origin_dsn = 'ifd'
))
UNION (
SELECT DISTINCT
c.companyname,
c.tradingas,
a.quarantined,
c.companyid,
a.address1,
a.city,
a.postcode,
a.region,
c.origin_dsn,
d.title,
d.firstname,
d.surname,
d.position,
a.telephoneno,
a.companyemail,
addresstypes,
markets
FROM companies c
join addresses a on c.companyid = a.companyid
join contacts d on c.companyid = d.companyid
where d.origin_dsn = 'ifd'
and primarycontact = 1
and d.clientid = 0
and c.origin_dsn = 'ifd'
and a.origin_dsn = 'ifd'
and c.companyid in (
select distinct companyid
from products
where type IN ('cr:f','cr:b')
AND origin_dsn = 'ifd')
)
) as t
where t.quarantined = 0
and t.origin_dsn = 'ifd'
and t.region IN (
'Northern Counties','North West','Yorkshire',
'East Midlands','West Midlands','South West',
'Home Counties','Southern Counties','Greater London',
'Scotland','Wales','Northern Ireland')
order by companyname

在 mysql 查询浏览器中运行需要惊人的 3.5 秒(在我的慢速、低规范笔记本电脑上)

此查询由 2 个非常相似的查询组成,每个查询大约需要 1.5 秒。

我的应用程序可能需要最多 4 个类似的联合。

谁能建议如何更有效地编写它?

最佳答案

我会将贵公司的产品标准首先移至预查询...然后加入以获取其他详细信息...因此您的公司 ID 只会预先选择一次。预查询,您可以按照我在此处采样的方式加入多个条件...

where 
( first set of criteria )
OR ( second set of criteria )
OR ( third set of criteria )

(但是,您的两个条件都使用 origin_dsn = 'ifd' ,这可能会被简化一次,但我不知道您的条件实际上是如何构造的)。

为了帮助优化查询,在您的产品表上,我会确保(Origin_DSN,type,material)的索引

对于地址表,索引为(companyid、origin_dsn、quaranted、region)

对于联系人表,索引为(companyid、origin_dsn、clientid)

类似...

SELECT DISTINCT 
c.companyname,
c.tradingas,
a.quarantined,
c.companyid,
a.address1,
a.city,
a.postcode,
a.region,
c.origin_dsn,
d.title,
d.firstname,
d.surname,
d.position,
a.telephoneno,
a.companyemail,
addresstypes,
markets
FROM
( select distinct
p.companyid
from
products p
where
( p.origin_dsn = 'ifd'
AND p.`type` IN ('cr:f','cr:b') )
OR ( p.origin_dsn = 'ifd'
AND p.`type` IN ('wd:f','wd:b')
AND p.material = 'PVCu'
AND p.description IN ('Windows and Doors','Vertical Sliders','Bi-Fold Doors') ) ) as PreQuery

JOIN companies c
ON PreQuery.CompanyID = c.CompanyID


join addresses a
on c.companyid = a.companyid
and c.origin_dsn = a.origin_dsn
and a.quarantined = 0
and a.Region in ( 'Northern Counties',
'North West',
'Yorkshire',
'East Midlands',
'West Midlands',
'South West',
'Home Counties',
'Southern Counties',
'Greater London',
'Scotland',
'Wales',
'Northern Ireland' )
join contacts d
on c.companyid = d.companyid
AND c.origin_dsn = d.origin_dsn
and d.clientid = 0
where
primarycontact = 1
and c.origin_dsn = 'ifd'
order by
c.companyname

关于mysql - 如何优化包含连接和子查询的 sql 联合,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16715921/

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