Comment 16 for bug 1918145

Attached a rough working patch (albeit, against Rocky for now) that demonstrates where my thinking is going.

Unfortunately, after a lot of thinking today, I could not come up with a solution that does not ultimately touch both neutron-lib and neutron to some degree. The crux of the problem always seems to be that I need a way to operate on the RBAC part of the query filter separately from the model side. For performance reasons, they really need to be treated separately -- that OUTER JOIN appearing in the subqueries is a killer, and splitting them up allows us to remove it.

I tried the force index patch just now, but on Percona MySQL 5.7, I still do not see the index being used and thus no performance improvements.

OTOH, this 'intrusive' patch speeds up API calls by over 2x in my lab environment with a couple thousand RBACs. The delay is mostly all in OSC/neutron-server and the database query takes msec instead of >1sec.
rocky/master: 3.927s~4.084s
preliminary RBAC query patch: 1.474s~1.629s