s-seitz opened a new issue, #6888:
URL: https://github.com/apache/cloudstack/issues/6888
<!--
Verify first that your issue/request is not already reported on GitHub.
Also test if the latest release and main branch are affected too.
Always add information AFTER of these HTML comments, but no need to delete
the comments.
-->
When using a broader IP Range (e.g multiple /16), a particular query is
getting slow:
~~~
SELECT COUNT(user_ip_address.public_ip_address) FROM user_ip_address INNER
JOIN vlan ON user_ip_address.vlan_db_id=vlan.id WHERE vlan.vlan_type =
'DirectAttached' AND user_ip_address.data_center_id = 1 AND
user_ip_address.state != 'Free' AND user_ip_address.removed IS NULL;
~~~
##### ISSUE TYPE
* Improvement Request
##### COMPONENT NAME
API, Mysql Backend
-->
##### CLOUDSTACK VERSION
4.17.1.0
##### CONFIGURATION
Advanced Networking, VLAN isolation
##### OS / ENVIRONMENT
Ubuntu 22.04 LTS
##### SUMMARY
When using a broader IP Range (e.g multiple /16), a particular query is
getting slow:
~~~
SELECT COUNT(user_ip_address.public_ip_address) FROM user_ip_address INNER
JOIN vlan ON user_ip_address.vlan_db_id=vlan.id WHERE vlan.vlan_type =
'DirectAttached' AND user_ip_address.data_center_id = 1 AND
user_ip_address.state != 'Free' AND user_ip_address.removed IS NULL;
~~~
This could be resolved by adding a (missing) key:
```
ALTER TABLE `user_ip_address` ADD KEY `fk_user_ip_address__state` (`state`);
```
##### STEPS TO REPRODUCE
Having a few /16 IPv4 Ranges assigned,
Enable mysql slowlog with a timeout of e.g. 2 seconds, and watch it happen.
##### EXPECTED RESULTS
Having the result faster.
##### ACTUAL RESULTS
Waiting for the result for about 2 seconds. (At least for my setup)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
