You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafficcontrol.apache.org by mi...@apache.org on 2017/01/10 03:38:49 UTC
[37/50] incubator-trafficcontrol git commit: removes expensive
DISTINCT
removes expensive DISTINCT
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/commit/b5964d85
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/tree/b5964d85
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/diff/b5964d85
Branch: refs/heads/master
Commit: b5964d8506d631225cbc672a41ae485515564845
Parents: a764127
Author: Jeremy Mitchell <mi...@gmail.com>
Authored: Tue Dec 20 16:53:18 2016 -0700
Committer: Dan Kirkwood <da...@gmail.com>
Committed: Sun Jan 8 21:05:01 2017 -0700
----------------------------------------------------------------------
.../Result/DeliveryServiceInfoForDomainList.pm | 65 +++++++++++---------
.../Result/DeliveryServiceInfoForServerList.pm | 5 +-
2 files changed, 38 insertions(+), 32 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/b5964d85/traffic_ops/app/lib/Schema/Result/DeliveryServiceInfoForDomainList.pm
----------------------------------------------------------------------
diff --git a/traffic_ops/app/lib/Schema/Result/DeliveryServiceInfoForDomainList.pm b/traffic_ops/app/lib/Schema/Result/DeliveryServiceInfoForDomainList.pm
index 438028c..7476054 100644
--- a/traffic_ops/app/lib/Schema/Result/DeliveryServiceInfoForDomainList.pm
+++ b/traffic_ops/app/lib/Schema/Result/DeliveryServiceInfoForDomainList.pm
@@ -38,41 +38,48 @@ __PACKAGE__->table("DeliveryServiceInfoForDomainList:");
__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance->view_definition( "
-SELECT DISTINCT
- deliveryservice.xml_id AS xml_id,
+SELECT
+ deliveryservice.xml_id,
deliveryservice.id AS ds_id,
- deliveryservice.dscp AS dscp,
- deliveryservice.signed AS signed,
- deliveryservice.qstring_ignore AS qstring_ignore,
- deliveryservice.org_server_fqdn as org_server_fqdn,
- deliveryservice.multi_site_origin as multi_site_origin,
- deliveryservice.multi_site_origin_algorithm as multi_site_origin_algorithm,
- deliveryservice.range_request_handling as range_request_handling,
- deliveryservice.origin_shield as origin_shield,
- regex.pattern AS pattern,
+ deliveryservice.dscp,
+ deliveryservice.signed,
+ deliveryservice.qstring_ignore,
+ deliveryservice.org_server_fqdn,
+ deliveryservice.multi_site_origin,
+ deliveryservice.multi_site_origin_algorithm,
+ deliveryservice.range_request_handling,
+ deliveryservice.origin_shield,
+ regex.pattern,
retype.name AS re_type,
dstype.name AS ds_type,
parameter.value AS domain_name,
- deliveryservice_regex.set_number AS set_number,
- deliveryservice.edge_header_rewrite as edge_header_rewrite,
- deliveryservice.mid_header_rewrite as mid_header_rewrite,
- deliveryservice.regex_remap as regex_remap,
- deliveryservice.cacheurl as cacheurl,
- deliveryservice.remap_text as remap_text,
- deliveryservice.protocol as protocol
+ deliveryservice_regex.set_number,
+ deliveryservice.edge_header_rewrite,
+ deliveryservice.mid_header_rewrite,
+ deliveryservice.regex_remap,
+ deliveryservice.cacheurl,
+ deliveryservice.remap_text,
+ deliveryservice.protocol
FROM
deliveryservice
- JOIN deliveryservice_regex ON deliveryservice_regex.deliveryservice = deliveryservice.id
- JOIN regex ON deliveryservice_regex.regex = regex.id
- JOIN type as retype ON regex.type = retype.id
- JOIN type as dstype ON deliveryservice.type = dstype.id
- JOIN profile_parameter ON deliveryservice.profile = profile_parameter.profile
- JOIN parameter ON parameter.id = profile_parameter.parameter
- JOIN deliveryservice_server ON deliveryservice_server.deliveryservice = deliveryservice.id
- JOIN server ON deliveryservice_server.server = server.id
-WHERE parameter.name = 'domain_name' AND parameter.value = ?
-ORDER BY ds_id, re_type , deliveryservice_regex.set_number
-"
+ JOIN deliveryservice_regex ON deliveryservice_regex.deliveryservice = deliveryservice.id
+ JOIN regex ON deliveryservice_regex.regex = regex.id
+ JOIN type as retype ON regex.type = retype.id
+ JOIN type as dstype ON deliveryservice.type = dstype.id
+ JOIN profile_parameter ON deliveryservice.profile = profile_parameter.profile
+ JOIN parameter ON parameter.id = profile_parameter.parameter
+WHERE
+ parameter.name = 'domain_name'
+ AND parameter.value = ?
+ AND deliveryservice.id in (
+ SELECT
+ deliveryservice_server.deliveryservice
+ FROM
+ deliveryservice_server)
+ORDER BY
+ ds_id,
+ re_type,
+ set_number"
);
__PACKAGE__->add_columns(
http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/b5964d85/traffic_ops/app/lib/Schema/Result/DeliveryServiceInfoForServerList.pm
----------------------------------------------------------------------
diff --git a/traffic_ops/app/lib/Schema/Result/DeliveryServiceInfoForServerList.pm b/traffic_ops/app/lib/Schema/Result/DeliveryServiceInfoForServerList.pm
index 72ff2b9..2c36f82 100644
--- a/traffic_ops/app/lib/Schema/Result/DeliveryServiceInfoForServerList.pm
+++ b/traffic_ops/app/lib/Schema/Result/DeliveryServiceInfoForServerList.pm
@@ -38,7 +38,7 @@ __PACKAGE__->table("DeliveryServiceInfoForServerList:");
__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance->view_definition( "
-SELECT DISTINCT
+SELECT
deliveryservice.xml_id AS xml_id,
deliveryservice.id AS ds_id,
deliveryservice.dscp AS dscp,
@@ -69,8 +69,7 @@ FROM
JOIN profile_parameter ON deliveryservice.profile = profile_parameter.profile
JOIN parameter ON parameter.id = profile_parameter.parameter
JOIN deliveryservice_server ON deliveryservice_server.deliveryservice = deliveryservice.id
- JOIN server ON deliveryservice_server.server = server.id
-WHERE parameter.name = 'domain_name' AND server.id IN (?)
+WHERE parameter.name = 'domain_name' AND deliveryservice_server.server = ?
ORDER BY ds_id, re_type , deliveryservice_regex.set_number
"
);