set sql_mode=no_engine_substitution;
eval set storage_engine = innodb;
set autocommit=1;

DROP DATABASE test;
CREATE DATABASE test;
USE test;
eval set storage_engine = myisam;

SELECT count(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_schema = 'test';
SELECT count(*) from INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'test';

drop procedure p1;
delimiter ^
create procedure p1(first int, last int)
begin
   SET @i=first;
   while @i <= last DO
      SET @stmt0 = CONCAT('CREATE TABLE t_', @i,
                   ' (f1 int not null auto_increment primary key)');
      PREPARE stmt0 FROM @stmt0;
      EXECUTE stmt0;
      SET @stmt1 = CONCAT('CREATE TABLE tt_', @i,
                   ' (f1 int not null auto_increment primary key,',
                   ' f2 int not null, ', 
                   ' f3 int, f5 int, f6 int, f7 int, f8 int, f9 int,', 
                   ' f10 int, f11 int, f12 int, f13 int, f14 int,', 
                   ' f15 int, f16 int, f17 int, f18 int, f19 int,', 
                   ' foreign key fk1(f2) references t_', @i, '(f1))');
      PREPARE stmt1 FROM @stmt1;
      EXECUTE stmt1;
      SET @stmt2 = CONCAT('insert into t_', @i,
                   ' values (), (), (), (), (), (), (), (), (), (), (),',
                   '        (), (), (), (), (), (), (), (), (), (), ()');

      PREPARE stmt2 FROM @stmt2;
      EXECUTE stmt2;
      SET @stmt3 = CONCAT('insert into tt_', @i, ' (f2)'
                   ' select f1 from t_', @i);
      PREPARE stmt3 FROM @stmt3;
      EXECUTE stmt3;
      EXECUTE stmt3;
      EXECUTE stmt3;
      SET @i = @i + 1;
   END while;
end;^
delimiter ;
call p1(100, 199);
call p1(200, 299);
call p1(300, 399);


create table KEY_COLUMN_USAGE  AS (select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE);
create table COLUMNS AS (select * from INFORMATION_SCHEMA.COLUMNS);


select now();
SELECT count(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
SELECT count(*) from INFORMATION_SCHEMA.COLUMNS;
SELECT t1.referenced_table_name, t1.referenced_column_name, t0.ORDINAL_POSITION,
t0.COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, EXTRA, COLUMN_DEFAULT, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS as t0
left outer 
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS t1 
ON t1.TABLE_SCHEMA =
t0.TABLE_SCHEMA AND t1.TABLE_NAME = t0.TABLE_NAME AND t1.COLUMN_NAME = t0.COLUMN_NAME
WHERE t0.TABLE_SCHEMA = schema() AND t0.TABLE_NAME = 'tt_1'
ORDER BY t0.TABLE_NAME, t0.ORDINAL_POSITION, t0.COLUMN_NAME, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
;
SELECT count(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
SELECT count(*) from INFORMATION_SCHEMA.COLUMNS;

select now();
SELECT count(*) FROM KEY_COLUMN_USAGE;
SELECT count(*) from COLUMNS;
SELECT t1.referenced_table_name, t1.referenced_column_name, t0.ORDINAL_POSITION,
t0.COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, EXTRA, COLUMN_DEFAULT, COLUMN_COMMENT
FROM `COLUMNS` as t0
left outer 
JOIN KEY_COLUMN_USAGE AS t1 
ON t1.TABLE_SCHEMA =
t0.TABLE_SCHEMA AND t1.TABLE_NAME = t0.TABLE_NAME AND t1.COLUMN_NAME = t0.COLUMN_NAME
WHERE t0.TABLE_SCHEMA = schema() AND t0.TABLE_NAME = 'tt_1'
ORDER BY t0.TABLE_NAME, t0.ORDINAL_POSITION, t0.COLUMN_NAME, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
;
SELECT count(*) FROM KEY_COLUMN_USAGE;
SELECT count(*) from COLUMNS;
select now();