From 3499d320d2e616a044b74db86fe4126de783174a Mon Sep 17 00:00:00 2001
From: Hiroyuki Morita <chiastolite.1980@gmail.com>
Date: Wed, 13 Dec 2017 10:25:59 +0900
Subject: Optimizing information_schema query for `foreign_keys`

Use CONSTRAINT_SCHEMA key for information_schema.referential_constraints.
See https://dev.mysql.com/doc/refman/5.7/en/information-schema-optimization.html.

```
> EXPLAIN SELECT fk.referenced_table_name AS 'to_table', fk.referenced_column_name AS 'primary_key', fk.column_name AS 'column', fk.constraint_name AS 'name', rc.update_rule AS 'on_update', rc.delete_rule AS 'on_delete' FROM information_schema.referential_constraints rc JOIN information_schema.key_column_usage fk USING (constraint_schema, constraint_name) WHERE fk.referenced_column_name IS NOT NULL AND fk.table_schema = 'activerecord_unittest' AND fk.table_name = 'fk_test_has_pk' AND rc.constraint_schema = 'activerecord_unittest' AND rc.table_name = 'fk_test_has_pk'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rc
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: CONSTRAINT_SCHEMA,TABLE_NAME
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using where; Open_full_table; Scanned 0 databases
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: fk
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: TABLE_SCHEMA,TABLE_NAME
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using where; Open_full_table; Scanned 0 databases; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
```
---
 .../lib/active_record/connection_adapters/abstract_mysql_adapter.rb  | 5 +++--
 1 file changed, 3 insertions(+), 2 deletions(-)

diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
index 479131caad..8569c76317 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -403,12 +403,13 @@ module ActiveRecord
                  fk.constraint_name AS 'name',
                  rc.update_rule AS 'on_update',
                  rc.delete_rule AS 'on_delete'
-          FROM information_schema.key_column_usage fk
-          JOIN information_schema.referential_constraints rc
+          FROM information_schema.referential_constraints rc
+          JOIN information_schema.key_column_usage fk
           USING (constraint_schema, constraint_name)
           WHERE fk.referenced_column_name IS NOT NULL
             AND fk.table_schema = #{scope[:schema]}
             AND fk.table_name = #{scope[:name]}
+            AND rc.constraint_schema = #{scope[:schema]}
             AND rc.table_name = #{scope[:name]}
         SQL
 
-- 
cgit v1.2.3