Comment 2 for bug 2071374

My proposal is to replace the joined relationship for rbac_entries inside SubnetPool DB model, because we don't need a many-to-many relationship here in my point of view. So, we can use the selectin eager loading to make this relationship one-to-many and create the model with only the necessary steps, without exploding into a cartesian product caused by the "left outer join" which is unnecessary in this model.

The "total" queries of this process would be splited into a series of smaller queries (slectin design) with much better performance, and the huge resulting query would be reduced to something like this:

$ time mysql --database neutron -e "SELECT subnets.project_id AS subnets_project_id, subnets.id AS subnets_id, subnets.in_use AS subnets_in_use, subnets.name AS subnets_name, subnets.network_id AS subnets_network_id, subnets.segment_id AS subnets_segment_id, subnets.subnetpool_id AS subnets_subnetpool_id, subnets.ip_version AS subnets_ip_version, subnets.cidr AS subnets_cidr, subnets.gateway_ip AS subnets_gateway_ip, subnets.enable_dhcp AS subnets_enable_dhcp, subnets.ipv6_ra_mode AS subnets_ipv6_ra_mode, subnets.ipv6_address_mode AS subnets_ipv6_address_mode, subnets.standard_attr_id AS subnets_standard_attr_id, subnetpools_1.shared AS subnetpools_1_shared, standardattributes_1.id AS standardattributes_1_id, standardattributes_1.resource_type AS standardattributes_1_resource_type, standardattributes_1.description AS standardattributes_1_description, standardattributes_1.revision_number AS standardattributes_1_revision_number, standardattributes_1.created_at AS standardattributes_1_created_at, standardattributes_1.updated_at AS standardattributes_1_updated_at, tags_1.standard_attr_id AS tags_1_standard_attr_id, tags_1.tag AS tags_1_tag, subnetpools_1.project_id AS subnetpools_1_project_id, subnetpools_1.id AS subnetpools_1_id, subnetpools_1.name AS subnetpools_1_name, subnetpools_1.ip_version AS subnetpools_1_ip_version, subnetpools_1.default_prefixlen AS subnetpools_1_default_prefixlen, subnetpools_1.min_prefixlen AS subnetpools_1_min_prefixlen, subnetpools_1.max_prefixlen AS subnetpools_1_max_prefixlen, subnetpools_1.is_default AS subnetpools_1_is_default, subnetpools_1.default_quota AS subnetpools_1_default_quota, subnetpools_1.hash AS subnetpools_1_hash, subnetpools_1.address_scope_id AS subnetpools_1_address_scope_id, subnetpools_1.standard_attr_id AS subnetpools_1_standard_attr_id, networkrbacs_1.project_id AS networkrbacs_1_project_id, networkrbacs_1.id AS networkrbacs_1_id, networkrbacs_1.target_project AS networkrbacs_1_target_project, networkrbacs_1.action AS networkrbacs_1_action, networkrbacs_1.object_id AS networkrbacs_1_object_id, standardattributes_2.id AS standardattributes_2_id, standardattributes_2.resource_type AS standardattributes_2_resource_type, standardattributes_2.description AS standardattributes_2_description, standardattributes_2.revision_number AS standardattributes_2_revision_number, standardattributes_2.created_at AS standardattributes_2_created_at, standardattributes_2.updated_at AS standardattributes_2_updated_at, tags_2.standard_attr_id AS tags_2_standard_attr_id, tags_2.tag AS tags_2_tag, subnet_dns_publish_fixed_ips_1.subnet_id AS subnet_dns_publish_fixed_ips_1_subnet_id, subnet_dns_publish_fixed_ips_1.dns_publish_fixed_ip AS subnet_dns_publish_fixed_ips_1_dns_publish_fixed_ip FROM subnets LEFT OUTER JOIN subnetpools AS subnetpools_1 ON subnets.subnetpool_id = subnetpools_1.id LEFT OUTER JOIN standardattributes AS standardattributes_1 ON standardattributes_1.id = subnetpools_1.standard_attr_id LEFT OUTER JOIN tags AS tags_1 ON standardattributes_1.id = tags_1.standard_attr_id LEFT OUTER JOIN networkrbacs AS networkrbacs_1 ON subnets.network_id = networkrbacs_1.object_id LEFT OUTER JOIN standardattributes AS standardattributes_2 ON standardattributes_2.id = subnets.standard_attr_id LEFT OUTER JOIN tags AS tags_2 ON standardattributes_2.id = tags_2.standard_attr_id LEFT OUTER JOIN subnet_dns_publish_fixed_ips AS subnet_dns_publish_fixed_ips_1 ON subnets.id = subnet_dns_publish_fixed_ips_1.subnet_id ORDER BY subnets.id ASC, subnets.standard_attr_id ASC;" > /tmp/output2

real 0m0.136s
user 0m0.057s
sys 0m0.020s

$ cat /tmp/output2 | wc -l
7602

In summary, We will have the same information and with a much faster final result.

$ time openstack subnet list | wc -l
5343

real 0m13.460s
user 0m4.884s
sys 0m0.121s

$ time openstack network list | wc -l
6813

real 0m18.060s
user 0m5.990s
sys 0m0.169s