Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
None
Description
When running queries to load constraints, there is a possible performance issue. it takes a long time to execute and load data from system tables:
How to reproduce:
1. create 100 databases, each db contains 1000 tables, each table contains 6 columns.
2. execute these queries:
SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS; |
SELECT |
CONSTRAINT_NAME,
|
COLUMN_NAME,
|
TABLE_NAME,
|
TABLE_SCHEMA,
|
REFERENCED_COLUMN_NAME,
|
REFERENCED_TABLE_NAME,
|
REFERENCED_TABLE_SCHEMA
|
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE; |
it takes ages to finish.
procs to generate env:
CREATE DEFINER = `root`@`localhost` PROCEDURE db1.gendbs () LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN |
DECLARE x INT; |
DECLARE str VARCHAR(255); |
|
|
SET x = 0; |
SET str = ''; |
|
|
loop_label:
|
LOOP
|
IF x > 99 |
THEN |
LEAVE loop_label;
|
END IF; |
|
|
SET x = x + 1; |
|
|
SET str = CONCAT('CREATE DATABASE gendb',x); |
|
EXECUTE IMMEDIATE str; |
|
|
END LOOP; |
END; |
|
|
|
CREATE DEFINER = `root`@`localhost` PROCEDURE db1.gentables () LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN |
DECLARE x INT; |
DECLARE y INT; |
DECLARE str VARCHAR(255); |
DECLARE str2 VARCHAR(255); |
|
|
SET x = 0; |
SET y =0; |
SET str = ''; |
SET str2 = |
' ( id INT NOT NULL, name CHAR(50) NOT NULL, address CHAR(50), city CHAR(50), state CHAR(25), zip_code CHAR(10) )'; |
|
|
OUTER_LOOP:
|
LOOP
|
IF y > 99 |
THEN |
LEAVE OUTER_LOOP;
|
END IF; |
|
|
SET y = y + 1; |
|
|
NESTED_LOOP:
|
LOOP
|
IF x >999 |
THEN |
LEAVE NESTED_LOOP;
|
END IF; |
|
|
SET x = x + 1; |
|
|
|
|
|
|
SET str = CONCAT('CREATE table ','gendb',y,'.','employee', x, str2); |
|
|
EXECUTE IMMEDIATE str; |
END LOOP; |
SET x =0; |
END LOOP; |
END; |