Comment 6 for bug 1918145

Hello,

after another attempts to add several networks, we have noticed that during the performance issue we do full table SCAN.

MariaDB [neutron]> EXPLAIN SELECT networkrbacs.project_id AS networkrbacs_project_id, networkrbacs.id AS networkrbacs_id, networkrbacs.target_tenant AS networkrbacs_target_tenant, networkrbacs.action AS networkrbacs_action, networkrbacs.object_id AS networkrbacs_objec t_id, anon_1.networks_id AS anon_1_networks_id
    -> FROM (SELECT networks.id AS networks_id
    -> FROM networks LEFT OUTER JOIN networkrbacs ON networks.id = networkrbacs.object_id
    -> WHERE networkrbacs.action = 'access_as_external' AND networkrbacs.target_tenant = 'a9b0XXXXXXX' OR network rbacs.target_tenant = '*' OR networks.project_id = 'a9b0XXXXXXX' OR networkrbacs.action = 'access_as_shared' AND (networkrbacs.target_tenant = 'a9b0XXXXXXX' OR networkrbacs.target_tenant = '*')) AS anon_1 INNER JOIN networkrba cs ON anon_1.networks_id = networkrbacs.object_id
    -> ;
+------+-------------+--------------+--------+--------------------------------+-----------+---------+--------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+------+-------------+--------------+--------+--------------------------------+-----------+---------+--------------------------------+-------+-------------+
| 1 | SIMPLE | networkrbacs | ALL | object_id | NULL | NULL | NULL |22857 | |
| 1 | SIMPLE | networks | eq_ref | PRIMARY,ix_networks_project_id | PRIMARY | 110 | neutron.networkrbacs.object_id |1 | |
| 1 | SIMPLE | networkrbacs | ref | object_id | object_id | 110 | neutron.networkrbacs.object_id |238 | Using where |
+------+-------------+--------------+--------+--------------------------------+-----------+---------+--------------------------------+-------+-------------+

After removing some of them, it seems we're under a certain threshold and we do use the index again.

MariaDB [neutron]> EXPLAIN SELECT networkrbacs.project_id AS networkrbacs_project_id, networkrbacs.id AS networkrbacs_id, networkrbacs.target_tenant AS networkrbacs_target_tenant, networkrbacs.action AS networkrbacs_action, networkrbacs.object_id AS networkrbacs_object_id, anon_1.networks_id AS anon_1_networks_id
    -> FROM (SELECT networks.id AS networks_id
    -> FROM networks LEFT OUTER JOIN networkrbacs ON networks.id = networkrbacs.object_id
    -> WHERE networkrbacs.action = 'access_as_external' AND networkrbacs.target_tenant = 'a9b0XXXXXXX' OR networkrbacs.target_tenant = '*' OR networks.project_id = 'a9b0XXXXXXX' OR networkrbacs.action = 'access_as_shared' AND (networkrbacs.target_tenant = 'a9b0XXXXXXX' OR networkrbacs.target_tenant = '*')) AS anon_1 INNER JOIN networkrbacs ON anon_1.networks_id = networkrbacs.object_id;
+------+-------------+--------------+-------+--------------------------------+------------------------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+-------+--------------------------------+------------------------+---------+---------------------+------+-------------+
| 1 | SIMPLE | networks | index | PRIMARY,ix_networks_project_id | ix_networks_project_id | 770 | NULL | 74 | Using index |
| 1 | SIMPLE | networkrbacs | ref | object_id | object_id | 110 | neutron.networks.id | 238 | Using where |
| 1 | SIMPLE | networkrbacs | ref | object_id | object_id | 110 | neutron.networks.id | 238 | |
+------+-------------+--------------+-------+--------------------------------+------------------------+---------+---------------------+------+-------------+
3 rows in set (0.000 sec)

PS: IDs have been anonymized.

Hope it helps.