From 70a01a484be3f32641120b53ab08e4429bf17c7b Mon Sep 17 00:00:00 2001 From: Miron Balcerzak Date: Fri, 28 Aug 2020 00:22:07 +0200 Subject: [PATCH] Bug #100665 - Suboptimal DatabaseMetaDataUsingInfoSchema#getImportedKeys() query - performance fix --- .../cj/jdbc/DatabaseMetaDataUsingInfoSchema.java | 11 ++++++----- 1 file changed, 6 insertions(+), 5 deletions(-) diff --git a/src/main/user-impl/java/com/mysql/cj/jdbc/DatabaseMetaDataUsingInfoSchema.java b/src/main/user-impl/java/com/mysql/cj/jdbc/DatabaseMetaDataUsingInfoSchema.java index e35127cd..0d95c53d 100644 --- a/src/main/user-impl/java/com/mysql/cj/jdbc/DatabaseMetaDataUsingInfoSchema.java +++ b/src/main/user-impl/java/com/mysql/cj/jdbc/DatabaseMetaDataUsingInfoSchema.java @@ -442,8 +442,8 @@ private String generateUpdateRuleClause() { db = this.pedantic ? db : StringUtils.unQuoteIdentifier(db, this.quotedId); StringBuilder sqlBuf = new StringBuilder( - this.databaseTerm.getValue() == DatabaseTerm.SCHEMA ? "SELECT A.CONSTRAINT_CATALOG AS PKTABLE_CAT, A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM," - : "SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,NULL AS PKTABLE_SCHEM,"); + this.databaseTerm.getValue() == DatabaseTerm.SCHEMA ? "SELECT DISTINCT A.CONSTRAINT_CATALOG AS PKTABLE_CAT, A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM," + : "SELECT DISTINCT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,NULL AS PKTABLE_SCHEM,"); sqlBuf.append(" A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,"); sqlBuf.append(this.databaseTerm.getValue() == DatabaseTerm.SCHEMA ? " A.TABLE_CATALOG AS FKTABLE_CAT, A.TABLE_SCHEMA AS FKTABLE_SCHEM," : " A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM,"); @@ -451,13 +451,14 @@ private String generateUpdateRuleClause() { sqlBuf.append(generateUpdateRuleClause()); sqlBuf.append(" AS UPDATE_RULE,"); sqlBuf.append(generateDeleteRuleClause()); - sqlBuf.append(" AS DELETE_RULE, A.CONSTRAINT_NAME AS FK_NAME, (SELECT CONSTRAINT_NAME FROM"); - sqlBuf.append(" INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND"); - sqlBuf.append(" TABLE_NAME = A.REFERENCED_TABLE_NAME AND CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1) AS PK_NAME,"); + sqlBuf.append(" AS DELETE_RULE, A.CONSTRAINT_NAME AS FK_NAME, TC.CONSTRAINT_NAME AS PK_NAME,"); sqlBuf.append(importedKeyNotDeferrable); sqlBuf.append(" AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A"); sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME) "); sqlBuf.append(generateOptionalRefContraintsJoin()); + sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON (A.REFERENCED_TABLE_SCHEMA = TC.TABLE_SCHEMA"); + sqlBuf.append(" AND A.REFERENCED_TABLE_NAME = TC.TABLE_NAME"); + sqlBuf.append(" AND TC.CONSTRAINT_TYPE IN ('UNIQUE', 'PRIMARY KEY'))"); sqlBuf.append("WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY'"); if (db != null) { sqlBuf.append(" AND A.TABLE_SCHEMA = ?");