Comment 54 for bug 1918145

An other interesting point.

I have noticed a query that takes about 50s, looks like it's to retrieve networks. With a simple change this one now takes 0.5s.

For some reasons that query returns 27840 rows, but let's not focus too much on that.

```
SELECT networks.project_id AS networks_project_id, networks.id AS networks_id, networks.name AS networks_name, networks.status AS networks_status, networks.admin_state_up AS networks_admin_state_up, networks.vlan_transparent AS networks_vlan_transparent, networks.availability_zone_hints AS networks_availability_zone_hints, networks.mtu AS networks_mtu, networks.standard_attr_id AS networks_standard_attr_id, 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, networkdnsdomains_1.network_id AS networkdnsdomains_1_network_id, networkdnsdomains_1.dns_domain AS networkdnsdomains_1_dns_domain, externalnetworks_1.network_id AS externalnetworks_1_network_id, externalnetworks_1.is_default AS externalnetworks_1_is_default, 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, networksegments_1.id AS networksegments_1_id, networksegments_1.network_id AS networksegments_1_network_id, networksegments_1.network_type AS networksegments_1_network_type, networksegments_1.physical_network AS networksegments_1_physical_network, networksegments_1.segmentation_id AS networksegments_1_segmentation_id, networksegments_1.is_dynamic AS networksegments_1_is_dynamic, networksegments_1.segment_index AS networksegments_1_segment_index, networksegments_1.name AS networksegments_1_name, networksegments_1.standard_attr_id AS networksegments_1_standard_attr_id, segmenthostmappings_1.segment_id AS segmenthostmappings_1_segment_id, segmenthostmappings_1.host AS segmenthostmappings_1_host, networksecuritybindings_1.network_id AS networksecuritybindings_1_network_id, networksecuritybindings_1.port_security_enabled AS networksecuritybindings_1_port_security_enabled, qos_network_policy_bindings_1.policy_id AS qos_network_policy_bindings_1_policy_id, qos_network_policy_bindings_1.network_id AS qos_network_policy_bindings_1_network_id FROM networks LEFT OUTER JOIN networkrbacs ON networks.id = networkrbacs.object_id LEFT OUTER JOIN standardattributes AS standardattributes_1 ON standardattributes_1.id = networks.standard_attr_id LEFT OUTER JOIN networkdnsdomains AS networkdnsdomains_1 ON networks.id = networkdnsdomains_1.network_id LEFT OUTER JOIN externalnetworks AS externalnetworks_1 ON networks.id = externalnetworks_1.network_id LEFT OUTER JOIN networksegments AS networksegments_1 ON networks.id = networksegments_1.network_id LEFT OUTER JOIN standardattributes AS standardattributes_2 ON standardattributes_2.id = networksegments_1.standard_attr_id LEFT OUTER JOIN segmenthostmappings AS segmenthostmappings_1 ON networksegments_1.id = segmenthostmappings_1.segment_id LEFT OUTER JOIN networksecuritybindings AS networksecuritybindings_1 ON networks.id = networksecuritybindings_1.network_id LEFT OUTER JOIN qos_network_policy_bindings AS qos_network_policy_bindings_1 ON networks.id = qos_network_policy_bindings_1.network_id WHERE networkrbacs.action = 'access_as_shared' AND (networkrbacs.target_tenant = '*' OR networkrbacs.target_tenant = 'd8df5c234580454ab8ea0aeb4903b03f') ORDER BY networks.id ASC, networks.standard_attr_id ASC;
```

As usually the query is doing outer join on networks to add details regarding several others tables like segments, dns domains...

We can notice the filter on networkrbacs only:

```
WHERE networkrbacs.action = 'access_as_shared' AND (networkrbacs.target_tenant = '*' OR networkrbacs.target_tenant = 'd8df5c234580454ab8ea0aeb4903b03f') ORDER BY networks.id ASC, networks.standard_attr_id ASC;
```

```
27840 rows in set (55.454 sec)
```

The filter is only based on `networkrbacs`. There are about 200 000 rows. Besides that `networks` has about 200 rows.

We know that a network can be associated to N rbacs so if we could filter networks that match the project_id we can expect a limited number or rows to examine for rbacs.

 I have updated the condition to add a filter on `networks.project_id` :

```
WHERE networks.project_id = 'd8df5c234580454ab8ea0aeb4903b03f' OR networkrbacs.action = 'access_as_shared' AND (networkrbacs.target_tenant = '*' OR networkrbacs.target_tenant = 'd8df5c234580454ab8ea0aeb4903b03f') ORDER BY networks.id ASC, networks.standard_attr_id ASC;
```

The result is now:

```
27840 rows in set (0.465 sec)
```

I understand the work that your are doing but I think the current model is quite good, it just needs to be optimized.

For that we should review the queries that takes time to identified issue and fix them. may be related to index or filter not well used.

I'm going to work on some of the queries to see how we can improve them. probably that one first.

What are your feeling guys?

@Tyler, do you think that you can do some tests in your env just to see if you get the same results?