aboutsummaryrefslogtreecommitdiffstats
path: root/activerecord
diff options
context:
space:
mode:
authorHiroyuki Morita <chiastolite.1980@gmail.com>2017-12-13 10:25:59 +0900
committerHiroyuki Morita <chiastolite.1980@gmail.com>2017-12-13 11:14:46 +0900
commit3499d320d2e616a044b74db86fe4126de783174a (patch)
treed464bc1394d6296e00fbd086ee212953f21a8176 /activerecord
parent9d43a84f73c1b3853a91d052a462ee60eccaf957 (diff)
downloadrails-3499d320d2e616a044b74db86fe4126de783174a.tar.gz
rails-3499d320d2e616a044b74db86fe4126de783174a.tar.bz2
rails-3499d320d2e616a044b74db86fe4126de783174a.zip
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) ```
Diffstat (limited to 'activerecord')
-rw-r--r--activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb5
1 files 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