首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >优化具有连接的MySQL查询

问优化具有连接的MySQL查询
EN

Stack Overflow用户
提问于 2019-08-08 19:08:24
回答 2查看 33关注 0票数 0

如何优化下面的查询。因为它有太多的连接

代码语言:javascript
复制
    SELECT `providers`.`provider_id`, `providers`.`providerFirstName`, `providers`.`providerLastName`, `providers`.`email`, `providers`.`phoneNumber`, `providers`.`address`, `providers`.`agencyName`, `providers`.`practiceCounty`, `providers`.`city`, `providers`.`state`, `providers`.`zip`, `providers`.`mondayHours`, `providers`.`tuesdayHours`, `providers`.`wednesdayHours`, `providers`.`thursdayHours`, `providers`.`fridayHours`, `providers`.`saturdayHours`, `providers`.`sundayHours`, `providers`.`additionalInfo`, `providers`.`website`, `providers`.`newClientsAcceptance`, `providers`.`updatedBy`, `providers`.`otherCategories`, `providers`.`otherInsurances`, `providers`.`otherTelehealthServices`, `providers`.`createdAt`, `providers`.`updatedAt`, `ProviderInsurances`.`provider_insurance_id` AS `ProviderInsurances.provider_insurance_id`, `ProviderInsurances`.`insurance_id` AS `ProviderInsurances.insurance_id`, `ProviderInsurances->Insurance`.`insurance_id` AS `ProviderInsurances.Insurance.insurance_id`, `ProviderInsurances->Insurance`.`insurance_name` AS `ProviderInsurances.Insurance.insurance_name`, `ProviderClients`.`provider_client_id` AS `ProviderClients.provider_client_id`, `ProviderClients`.`client_id` AS `ProviderClients.client_id`, `ProviderClients->Client`.`client_id` AS `ProviderClients.Client.client_id`, `ProviderClients->Client`.`client_name` AS `ProviderClients.Client.client_name`, `ProviderCounties`.`provider_county_id` AS `ProviderCounties.provider_county_id`, `ProviderCounties`.`county_id` AS `ProviderCounties.county_id`, `ProviderCounties->County`.`county_id` AS `ProviderCounties.County.county_id`, `ProviderCounties->County`.`county_name` AS `ProviderCounties.County.county_name`, `ProviderSpecialities`.`provider_speciality_id` AS `ProviderSpecialities.provider_speciality_id`, `ProviderSpecialities`.`speciality_id` AS `ProviderSpecialities.speciality_id`, `ProviderSpecialities->Speciality`.`speciality_id` AS `ProviderSpecialities.Speciality.speciality_id`, `ProviderSpecialities->Speciality`.`speciality_name` AS `ProviderSpecialities.Speciality.speciality_name`, `ProviderCategories`.`provider_category_id` AS `ProviderCategories.provider_category_id`, `ProviderCategories`.`category_id` AS `ProviderCategories.category_id`, `ProviderCategories->Category`.`category_id` AS `ProviderCategories.Category.category_id`, `ProviderCategories->Category`.`category_name` AS `ProviderCategories.Category.category_name`, `Telehealths`.`telehealth_id` AS `Telehealths.telehealth_id`, `Telehealths`.`telehealth_name` AS `Telehealths.telehealth_name` 
FROM `providers` AS `providers` 
LEFT OUTER JOIN `provider_insurances` AS `ProviderInsurances` ON `providers`.`provider_id` = `ProviderInsurances`.`provider_id` 
LEFT OUTER JOIN `insurance` AS `ProviderInsurances->Insurance` ON `ProviderInsurances`.`insurance_id` = `ProviderInsurances->Insurance`.`insurance_id` 
LEFT OUTER JOIN `provider_clients` AS `ProviderClients` ON `providers`.`provider_id` = `ProviderClients`.`provider_id` 
LEFT OUTER JOIN `clients` AS `ProviderClients->Client` ON `ProviderClients`.`client_id` = `ProviderClients->Client`.`client_id` 
LEFT OUTER JOIN `provider_counties` AS `ProviderCounties` ON `providers`.`provider_id` = `ProviderCounties`.`provider_id` 
LEFT OUTER JOIN `counties` AS `ProviderCounties->County` ON `ProviderCounties`.`county_id` = `ProviderCounties->County`.`county_id` 
LEFT OUTER JOIN `provider_specialities` AS `ProviderSpecialities` ON `providers`.`provider_id` = `ProviderSpecialities`.`provider_id` 
LEFT OUTER JOIN `specialities` AS `ProviderSpecialities->Speciality` ON `ProviderSpecialities`.`speciality_id` = `ProviderSpecialities->Speciality`.`speciality_id` 
LEFT OUTER JOIN `provider_categories` AS `ProviderCategories` ON `providers`.`provider_id` = `ProviderCategories`.`provider_id` 
LEFT OUTER JOIN `categories` AS `ProviderCategories->Category` ON `ProviderCategories`.`category_id` = `ProviderCategories->Category`.`category_id` 
LEFT OUTER JOIN `telehealth` AS `Telehealths` ON `providers`.`provider_id` = `Telehealths`.`provider_id` 
WHERE `providers`.`updatedAt` 
BETWEEN '2019-08-06 18:30:00' AND '2019-08-08 00:00:00';

减少执行时间的最佳实践是什么?请帮帮忙。

下面我添加了"EXPLAIN SELECT“输出

​

EN

回答 2

Stack Overflow用户

发布于 2019-08-08 19:12:12

确保在join中涉及的列上有正确的索引,例如:

为

代码语言:javascript
复制
table  provider_insurances columns  (provider_id, insurance_id)
table  insurance columns (insurance_id)
table  provider_clients columns (provider_id)
...
....
table  providers columns (updatedAt, provider_id )
票数 -1
EN

Stack Overflow用户

发布于 2019-08-08 19:12:12

您可以使用sequelize ORM来处理查询联接。链接=> [https://sequelize.org/master/manual/querying.html]

票数 -1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/57411183

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档