This is an automated email from the ASF dual-hosted git repository.
dahn pushed a commit to branch 4.22
in repository https://gitbox.apache.org/repos/asf/cloudstack.git
The following commit(s) were added to refs/heads/4.22 by this push:
new 58916eb6080 Use lateral join (introduced in MySQL 8.0.14) with
subquery on user_statistics table in account_view for netstats (#12631)
58916eb6080 is described below
commit 58916eb608036669c3fabe0239b339745b8475cf
Author: Suresh Kumar Anaparti <[email protected]>
AuthorDate: Fri Mar 6 00:48:58 2026 +0530
Use lateral join (introduced in MySQL 8.0.14) with subquery on
user_statistics table in account_view for netstats (#12631)
---
.../META-INF/db/schema-42200to42210-cleanup.sql | 2 ++
.../db/views/cloud.account_netstats_view.sql | 31 ----------------------
.../META-INF/db/views/cloud.account_view.sql | 15 ++++++++---
3 files changed, 13 insertions(+), 35 deletions(-)
diff --git
a/engine/schema/src/main/resources/META-INF/db/schema-42200to42210-cleanup.sql
b/engine/schema/src/main/resources/META-INF/db/schema-42200to42210-cleanup.sql
index 54baf226ac4..505c8ef5715 100644
---
a/engine/schema/src/main/resources/META-INF/db/schema-42200to42210-cleanup.sql
+++
b/engine/schema/src/main/resources/META-INF/db/schema-42200to42210-cleanup.sql
@@ -18,3 +18,5 @@
--;
-- Schema upgrade cleanup from 4.22.0.0 to 4.22.1.0
--;
+
+DROP VIEW IF EXISTS `cloud`.`account_netstats_view`;
diff --git
a/engine/schema/src/main/resources/META-INF/db/views/cloud.account_netstats_view.sql
b/engine/schema/src/main/resources/META-INF/db/views/cloud.account_netstats_view.sql
deleted file mode 100644
index 11193c465fd..00000000000
---
a/engine/schema/src/main/resources/META-INF/db/views/cloud.account_netstats_view.sql
+++ /dev/null
@@ -1,31 +0,0 @@
--- Licensed to the Apache Software Foundation (ASF) under one
--- or more contributor license agreements. See the NOTICE file
--- distributed with this work for additional information
--- regarding copyright ownership. The ASF licenses this file
--- to you under the Apache License, Version 2.0 (the
--- "License"); you may not use this file except in compliance
--- with the License. You may obtain a copy of the License at
---
--- http://www.apache.org/licenses/LICENSE-2.0
---
--- Unless required by applicable law or agreed to in writing,
--- software distributed under the License is distributed on an
--- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
--- KIND, either express or implied. See the License for the
--- specific language governing permissions and limitations
--- under the License.
-
--- cloud.account_netstats_view source
-
-
-DROP VIEW IF EXISTS `cloud`.`account_netstats_view`;
-
-CREATE VIEW `cloud`.`account_netstats_view` AS
-select
- `user_statistics`.`account_id` AS `account_id`,
- (sum(`user_statistics`.`net_bytes_received`) +
sum(`user_statistics`.`current_bytes_received`)) AS `bytesReceived`,
- (sum(`user_statistics`.`net_bytes_sent`) +
sum(`user_statistics`.`current_bytes_sent`)) AS `bytesSent`
-from
- `user_statistics`
-group by
- `user_statistics`.`account_id`;
diff --git
a/engine/schema/src/main/resources/META-INF/db/views/cloud.account_view.sql
b/engine/schema/src/main/resources/META-INF/db/views/cloud.account_view.sql
index edc164c40cb..327c6c627e2 100644
--- a/engine/schema/src/main/resources/META-INF/db/views/cloud.account_view.sql
+++ b/engine/schema/src/main/resources/META-INF/db/views/cloud.account_view.sql
@@ -39,8 +39,8 @@ select
`data_center`.`id` AS `data_center_id`,
`data_center`.`uuid` AS `data_center_uuid`,
`data_center`.`name` AS `data_center_name`,
- `account_netstats_view`.`bytesReceived` AS `bytesReceived`,
- `account_netstats_view`.`bytesSent` AS `bytesSent`,
+ `account_netstats`.`bytesReceived` AS `bytesReceived`,
+ `account_netstats`.`bytesSent` AS `bytesSent`,
`vmlimit`.`max` AS `vmLimit`,
`vmcount`.`count` AS `vmTotal`,
`runningvm`.`vmcount` AS `runningVms`,
@@ -89,8 +89,15 @@ from
`cloud`.`domain` ON account.domain_id = domain.id
left join
`cloud`.`data_center` ON account.default_zone_id = data_center.id
- left join
- `cloud`.`account_netstats_view` ON account.id =
account_netstats_view.account_id
+ left join lateral (
+ select
+ coalesce(sum(`user_statistics`.`net_bytes_received` +
`user_statistics`.`current_bytes_received`), 0) AS `bytesReceived`,
+ coalesce(sum(`user_statistics`.`net_bytes_sent` +
`user_statistics`.`current_bytes_sent`), 0) AS `bytesSent`
+ from
+ `cloud`.`user_statistics`
+ where
+ `user_statistics`.`account_id` = `account`.`id`
+ ) AS `account_netstats` ON TRUE
left join
`cloud`.`resource_limit` vmlimit ON account.id = vmlimit.account_id
and vmlimit.type = 'user_vm' and vmlimit.tag IS NULL
