Listing ports in a project with large number of ports results in querying the database which isn't efficient
Steps to reproduce:
1. Create 2000 ports in 20 different networks(the more the better) in the same project.
2. Enable query log for mysql using `slow_query_log` and setting `long_query_time` to 0.1 seconds
3. Now list the ports using `openstack port list` as non-admin user
4. The following query should be logged in the `slow_query_log_file`: https://paste.opendev.org/show/819649/
5. Here is part of the query that is relevant for the bug:
```
SELECT DISTINCT ports.project_id AS ports_project_id, ports.id AS ports_id, ports.name AS ports_name, ports.network_id AS ports_network_id, ports.mac_address AS ports_mac_address, ports.admin_state_up AS ports_admin_state_up, ports.status AS ports_status, ports.device_id AS ports_device_id, ports.device_owner AS ports_device_owner, ports.ip_allocation AS ports_ip_allocation, ports.standard_attr_id AS ports_standard_attr_id
FROM ports, networks
WHERE ports.project_id = '<project-id>' OR ports.network_id = networks.id AND networks.project_id = '<project-id>' ORDER BY ports.id ASC
```
6. Executing the above query responds in about 0.25 seconds <-- Which is the problem.
7. Now update this query to use *explicit join* instead of implicit on:
```
SELECT DISTINCT ports.project_id AS ports_project_id, ports.id AS ports_id, ports.name AS ports_name, ports.network_id
AS ports_network_id, ports.mac_address AS ports_mac_address, ports.admin_state_up AS ports_admin_state_up, ports.status AS ports_status, ports.device_id AS ports_device_id, ports.device_owner AS ports_device_owner, ports.ip_allocation AS ports_ip_allocation, ports.standard_attr_id AS ports_standard_attr_id
FROM ports JOIN networks ON ports.network_id = networks.id
WHERE ports.project_id = '<project-id>' AND networks.project_id = '<project-id>' ORDER BY ports.id ASC;
```
8. The response time of this is around 0.02 seconds
On our production environment(Yoga version) we see an extreme scenario of this, we have one project which has about 350 ports and 2 networks. And for that project, the query responds in 15 seconds, where as the optimized one responds in 0.01 seconds.
In total we have about 1000 projects and most of the projects have about 10 ports.
Version:
- Openstack version, neutron from trunk. CommitID 28961c8b76a4b09412825231a3f69374b183aefd
- Single node devstack on Ubuntu 22.04.02 LTS
Fix proposed here
https:/