pt-online-schema-change find_child_tables slow

tested on
pt-online-schema-change 3.0.2
mysql Ver 14.14 Distrib 5.6.35-80.0, for debian-linux-gnu (x86_64) using readline 6.3
pt-online-schema-change D=himalaya_novocar_brainstorm,t=Estimation,u=root,p=***** --alter-foreign-keys-method=auto --alter="MODIFY type set('INVOICE','PLANNING','ATR')" --dry-run

There are a couple of tickets referencing the inherit slowness of information schema. Most are closed because you can set --alter-foreign-keys-method to none, so it doesn't do the check.

But, this does not solve the problem. It simply avoids it. What if you actually do want the foreign-keys ?

The real problem is that the query in pt-online-schema-change that is used to find the child tables in sub find_child_tables is slow:

Currently:
my $sql = "SELECT table_schema, table_name "
. "FROM information_schema.key_column_usage "
. "WHERE referenced_table_schema='$tbl->

{db}' "
. "AND referenced_table_name='$tbl->{tbl}'";

But... simply adding "table_schema='$tbl->{db}

'" will speed this up.
I propose:
my $sql = "SELECT table_schema, table_name "
. "FROM information_schema.key_column_usage "
. "WHERE table_schema='$tbl->

{db}' AND referenced_table_schema='$tbl->{db}

' "
. "AND referenced_table_name='$tbl->

{tbl}

'";

In the current version, foreign keys that are made across databases will be included. I my proposal, only foreign keys from the same database will be checked.
I can't find a reason why anyone would make a foreign key cross databases, but I'm sure there are some that do have a valid use case for this.

I believe the default way should only check the same table_schema. And if people want to check cross database they can do this using a extra flag?

I'm reporting this as a bug and not a blueprint, because the current code is simply not usable when working with a lot of databases. I hope this is ok.

Launchpad: https://bugs.launchpad.net/percona-toolkit/+bug/1690122