read_schema => 1 can take a long time with Mojo::mysql

[joshrabinowitz]

If I use the attached SQL to create mysql tables and then use Yancy's read_schema=>1 option on them, it takes several seconds to complete.

tables.txt (I had to name the file tables.txt instead of tables.sql, or github wouldn't let me attach it)

[preaction]

On my laptop, it only ever took about a second, but I think I see the source of the issue: The backend is executing <tables>**2 queries to get foreign keys. With your 100+ tables, that's 10000+ queries. This patch reduces it to just one per table. Could you give it a try and see if that fixes it?

diff --git a/lib/Yancy/Backend/Role/Relational.pm b/lib/Yancy/Backend/Role/Relational.pm
index 6e67785..9173854 100644
--- a/lib/Yancy/Backend/Role/Relational.pm
+++ b/lib/Yancy/Backend/Role/Relational.pm
@@ -411,10 +411,8 @@ sub read_schema {
     # Foreign keys
     for my $table ( @table_names ) {
         my @foreign_keys;
-        for my $foreign_table ( @table_names ) {
-            my $sth = $db->dbh->foreign_key_info( $dbcatalog, $dbschema, $foreign_table, $dbcatalog, $dbschema, $table );
-            next unless $sth; # Pg returns null if no foreign keys
-            push @foreign_keys, @{ $sth->fetchall_arrayref( {} ) };
+        if ( my $sth = $db->dbh->foreign_key_info( (undef)x3, $dbcatalog, $dbschema, $table ) ) {
+            @foreign_keys = @{ $sth->fetchall_arrayref( {} ) };
         }

         for my $fk ( @foreign_keys ) {