MySQL performance issue with lint command with some tables #1727

jaugustin · 2023-06-08T10:39:44Z

Hello,

I hit a performance issue when running the lint command.

I created a repo to reproduce the issue https://github.com/jaugustin/atlas_slow_lint

If I run the atlas migrate lint --latest 1 --dev-url docker://mysql/8/dev then it get stuck for 15 minutes

The issue is from a mysql command executed to get tables / foreign keys that get stuck in checking permissions for some time

I can try to provide more information if needed

The text was updated successfully, but these errors were encountered:

jaugustin · 2023-06-08T10:40:46Z

@a8m I tag you as asked on discord 😉

jaugustin · 2023-06-08T12:23:00Z

the root issue is from the query here https://github.com/ariga/atlas/blob/master/sql/mysql/inspect.go#L770

I have an explain output

explain SELECT 
t1.CONSTRAINT_NAME, 
t1.TABLE_NAME, 
t1.COLUMN_NAME,
 t1.TABLE_SCHEMA, 
t1.REFERENCED_TABLE_NAME, 
t1.REFERENCED_COLUMN_NAME, 
t1.REFERENCED_TABLE_SCHEMA, 
t3.UPDATE_RULE, t3.DELETE_RULE 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS t1 
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t2 
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS t3 ON t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME AND t1.CONSTRAINT_NAME = t3.CONSTRAINT_NAME AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA AND t1.TABLE_SCHEMA = t3.CONSTRAINT_SCHEMA 
WHERE t2.CONSTRAINT_TYPE = 'FOREIGN KEY' AND t1.TABLE_SCHEMA = 'dev' AND t1.TABLE_NAME 
IN ('atlas_schema_revisions', 'table_01', 'table_02', 'table_03', 'table_04', 'table_05', 'table_06', 'table_07', 'table_08', 'table_09', 'table_10', 'table_101', 'table_102', 'table_103', 'table_104', 'table_105', 'table_106', 'table_107', 'table_108', 'table_109', 'table_11', 'table_110', 'table_111', 'table_112', 'table_113', 'table_114', 'table_115', 'table_116', 'table_117', 'table_118', 'table_119', 'table_12', 'table_120', 'table_121', 'table_122', 'table_123', 'table_124', 'table_125', 'table_126', 'table_127', 'table_128', 'table_129', 'table_13', 'table_130', 'table_131', 'table_132', 'table_133', 'table_134', 'table_135', 'table_136', 'table_137', 'table_138', 'table_139', 'table_14', 'table_140', 'table_141', 'table_142', 'table_143', 'table_144', 'table_145', 'table_146', 'table_147', 'table_148', 'table_149', 'table_15', 'table_150', 'table_151', 'table_152', 'table_153', 'table_154', 'table_155', 'table_156', 'table_157', 'table_158', 'table_159', 'table_16', 'table_160', 'table_161', 'table_162', 'table_163', 'table_164', 'table_165', 'table_166', 'table_167', 'table_168', 'table_169', 'table_17', 'table_170', 'table_171', 'table_172', 'table_173', 'table_174', 'table_175', 'table_176', 'table_177', 'table_178', 'table_179', 'table_18', 'table_180', 'table_181', 'table_182', 'table_183', 'table_184', 'table_185', 'table_186', 'table_187', 'table_188', 'table_189', 'table_19', 'table_190', 'table_191', 'table_192', 'table_193', 'table_194', 'table_195', 'table_196', 'table_197', 'table_198', 'table_199', 'table_20', 'table_201', 'table_202', 'table_203', 'table_204', 'table_205', 'table_206', 'table_207', 'table_208', 'table_209', 'table_21', 'table_210', 'table_211', 'table_212', 'table_213', 'table_214', 'table_215', 'table_216', 'table_217', 'table_218', 'table_219', 'table_22', 'table_220', 'table_221',
'table_222', 'table_223', 'table_224', 'table_225', 'table_226', 'table_227', 'table_228', 'table_229', 'table_23', 'table_230', 'table_231', 'table_232', 'table_233', 'table_234', 'table_235', 'table_236', 'table_237', 'table_238', 'table_239', 'table_24', 'table_240', 'table_241', 'table_242', 'table_243', 'table_244', 'table_245', 'table_246', 'table_247', 'table_248', 'table_249', 'table_25', 'table_250', 'table_251', 'table_252', 'table_253', 'table_254', 'table_255', 'table_256', 'table_257', 'table_258', 'table_259', 'table_26', 'table_260', 'table_261', 'table_262', 'table_263', 'table_264', 'table_265', 'table_266', 'table_267', 'table_268', 'table_269', 'table_27', 'table_270', 'table_271', 'table_272', 'table_273', 'table_274', 'table_275', 'table_276', 'table_277', 'table_278', 'table_279', 'table_28', 'table_280', 'table_281', 'table_282', 'table_283', 'table_284', 'table_285', 'table_286', 'table_287', 'table_288', 'table_289', 'table_29', 'table_290', 'table_291', 'table_292', 'table_293', 'table_294', 'table_295', 'table_296', 'table_297', 'table_298', 'table_299', 'table_30', 'table_301', 'table_302', 'table_303', 'table_304', 'table_305', 'table_306', 'table_307', 'table_308', 'table_309', 'table_31', 'table_310', 'table_311', 'table_312', 'table_313', 'table_314', 'table_315', 'table_316', 'table_317', 'table_318', 'table_319', 'table_32', 'table_320', 'table_321', 'table_322', 'table_323', 'table_324', 'table_325', 'table_326', 'table_327', 'table_328', 'table_329', 'table_33', 'table_330', 'table_331', 'table_332', 'table_333', 'table_334', 'table_335', 'table_336', 'table_337', 'table_338',
'table_339', 'table_34', 'table_340', 'table_341', 'table_342', 'table_343', 'table_344', 'table_345', 'table_346', 'table_347', 'table_348', 'table_349', 'table_35', 'table_350', 'table_351', 'table_352', 'table_353', 'table_354', 'table_355', 'table_356', 'table_357', 'table_358', 'table_359', 'table_36', 'table_360', 'table_361', 'table_362', 'table_363', 'table_364', 'table_365', 'table_366', 'table_367', 'table_368', 'table_369', 'table_37', 'table_370', 'table_371', 'table_372', 'table_373', 'table_374', 'table_375', 'table_376', 'table_377', 'table_378', 'table_379', 'table_38', 'table_380', 'table_381', 'table_382', 'table_383', 'table_384', 'table_385', 'table_386', 'table_387', 'table_388', 'table_389', 'table_39', 'table_390', 'table_391', 'table_392', 'table_393', 'table_394', 'table_395', 'table_396', 'table_397', 'table_398', 'table_399', 'table_40', 'table_41', 'table_42', 'table_43', 'table_44', 'table_45', 'table_46', 'table_47', 'table_48', 'table_49', 'table_50', 'table_51', 'table_52', 'table_53', 'table_54', 'table_55', 'table_56', 'table_57', 'table_58', 'table_59', 'table_60', 'table_61', 'table_62', 'table_63', 'table_64', 'table_65', 'table_66', 'table_67', 'table_68', 'table_69', 'table_70', 'table_71', 'table_72', 'table_73', 'table_74', 'table_75', 'table_76', 'table_77', 'table_78', 'table_79', 'table_80', 'table_81', 'table_82', 'table_83', 'table_84', 'table_85', 'table_86', 'table_87', 'table_88', 'table_89', 'table_90', 'table_91', 'table_92', 'table_93', 'table_94', 'table_95', 'table_96', 'table_97', 'table_98', 'table_99') ORDER BY t1.CONSTRAINT_NAME, t1.ORDINAL_POSITION;
+----+-------------------+------------+------------+--------+------------------------+-------------+---------+-----------------------------+------+----------+----------------------------------------------+
| id | select_type       | table      | partitions | type   | possible_keys          | key         | key_len | ref                         | rows | filtered | Extra                                        |
+----+-------------------+------------+------------+--------+------------------------+-------------+---------+-----------------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY           | cat        | NULL       | index  | PRIMARY                | name        | 194     | NULL                        |    1 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | PRIMARY           | cat        | NULL       | index  | PRIMARY                | name        | 194     | NULL                        |    1 |   100.00 | Using index; Using join buffer (hash join)   |
|  1 | PRIMARY           | cat        | NULL       | index  | PRIMARY                | name        | 194     | NULL                        |    1 |   100.00 | Using index; Using join buffer (hash join)   |
|  1 | PRIMARY           | sch        | NULL       | eq_ref | PRIMARY,catalog_id     | catalog_id  | 202     | mysql.cat.id,const          |    1 |   100.00 | Using index                                  |
|  1 | PRIMARY           | sch        | NULL       | eq_ref | PRIMARY,catalog_id     | catalog_id  | 202     | mysql.cat.id,const          |    1 |   100.00 | Using index                                  |
|  1 | PRIMARY           | sch        | NULL       | eq_ref | PRIMARY,catalog_id     | catalog_id  | 202     | mysql.cat.id,const          |    1 |   100.00 | Using index                                  |
|  1 | PRIMARY           | tbl        | NULL       | ref    | schema_id              | schema_id   | 8       | mysql.sch.id                |  131 |    50.00 | Using where; Rematerialize (<derived3>)      |
|  1 | PRIMARY           | tbl        | NULL       | ref    | schema_id              | schema_id   | 8       | mysql.sch.id                |  131 |   100.00 | Using where; Rematerialize (<derived7>)      |
|  1 | PRIMARY           | fk         | NULL       | ALL    | schema_id,table_id     | NULL        | NULL    | NULL                        |  854 |   100.00 | Using where                                  |
|  1 | PRIMARY           | tbl        | NULL       | eq_ref | PRIMARY                | PRIMARY     | 8       | mysql.fk.table_id           |    1 |   100.00 | Using where                                  |
|  1 | PRIMARY           | <derived3> | NULL       | ref    | <auto_key0>            | <auto_key0> | 195     | func                        |    2 |   100.00 | Using where                                  |
|  1 | PRIMARY           | col        | NULL       | eq_ref | PRIMARY                | PRIMARY     | 8       | constraints.column_id       |    1 |   100.00 | Using where                                  |
|  1 | PRIMARY           | <derived7> | NULL       | ref    | <auto_key0>            | <auto_key0> | 195     | constraints.CONSTRAINT_NAME |    2 |   100.00 | NULL                                         |
|  7 | DEPENDENT DERIVED | idx        | NULL       | ref    | table_id               | table_id    | 8       | mysql.tbl.id                |    2 |    40.00 | Using where                                  |
|  8 | DEPENDENT UNION   | fk         | NULL       | ref    | table_id               | table_id    | 8       | mysql.tbl.id                |    2 |   100.00 | Using index                                  |
|  9 | DEPENDENT UNION   | NULL       | NULL       | NULL   | NULL                   | NULL        | NULL    | NULL                        | NULL |     NULL | Impossible WHERE                             |
|  3 | DEPENDENT DERIVED | idx        | NULL       | ref    | PRIMARY,table_id       | table_id    | 8       | mysql.tbl.id                |    2 |    40.00 | Using where                                  |
|  3 | DEPENDENT DERIVED | icu        | NULL       | ref    | index_id,index_id_2    | index_id_2  | 8       | mysql.idx.id                |    3 |   100.00 | Using index                                  |
|  4 | DEPENDENT UNION   | fk         | NULL       | ref    | PRIMARY,table_id       | table_id    | 8       | mysql.tbl.id                |    2 |   100.00 | NULL                                         |
|  4 | DEPENDENT UNION   | fkcu       | NULL       | ref    | PRIMARY,foreign_key_id | PRIMARY     | 8       | mysql.fk.id                 |    1 |   100.00 | NULL                                         |
+----+-------------------+------------+------------+--------+------------------------+-------------+---------+-----------------------------+------+----------+----------------------------------------------+
20 rows in set, 7 warnings (0.00 sec)

And the related warnings:

show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1276 | Field or reference 'mysql.tbl.id' of SELECT #3 was resolved in SELECT #2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Note  | 1276 | Field or reference 'mysql.tbl.id' of SELECT #4 was resolved in SELECT #2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Note  | 1276 | Field or reference 'mysql.tbl.id' of SELECT #7 was resolved in SELECT #6                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Note  | 1276 | Field or reference 'mysql.tbl.hidden' of SELECT #7 was resolved in SELECT #6                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| Note  | 1276 | Field or reference 'mysql.tbl.id' of SELECT #8 was resolved in SELECT #6                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Note  | 1276 | Field or reference 'mysql.tbl.id' of SELECT #9 was resolved in SELECT #6                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| Note  | 1003 | /* select#1 */ select `constraints`.`CONSTRAINT_NAME` AS `CONSTRAINT_NAME`,`mysql`.`tbl`.`name` AS `TABLE_NAME`,(`mysql`.`col`.`name` collate utf8mb3_tolower_ci) AS `COLUMN_NAME`,`mysql`.`sch`.`name` AS `TABLE_SCHEMA`,`constraints`.`REFERENCED_TABLE_NAME` AS `REFERENCED_TABLE_NAME`,`constraints`.`REFERENCED_COLUMN_NAME` AS `REFERENCED_COLUMN_NAME`,`constraints`.`REFERENCED_TABLE_SCHEMA` AS `REFERENCED_TABLE_SCHEMA`,`mysql`.`fk`.`update_rule` AS `UPDATE_RULE`,`mysql`.`fk`.`delete_rule` AS `DELETE_RULE` from `mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` join lateral (/* select#3 */ select `mysql`.`idx`.`name` AS `CONSTRAINT_NAME`,`mysql`.`icu`.`ordinal_position` AS `ORDINAL_POSITION`,NULL AS `POSITION_IN_UNIQUE_CONSTRAINT`,NULL AS `REFERENCED_TABLE_SCHEMA`,NULL AS `REFERENCED_TABLE_NAME`,NULL AS `REFERENCED_COLUMN_NAME`,`mysql`.`icu`.`column_id` AS `column_id`,((0 <> `mysql`.`idx`.`hidden`) or (0 <> `mysql`.`icu`.`hidden`)) AS `HIDDEN` from `mysql`.`indexes` `idx` join `mysql`.`index_column_usage` `icu` where ((`mysql`.`icu`.`index_id` = `mysql`.`idx`.`id`) and (`mysql`.`idx`.`table_id` = `mysql`.`tbl`.`id`) and (`mysql`.`idx`.`type` in ('PRIMARY','UNIQUE'))) union all /* select#4 */ select (`mysql`.`fk`.`name` collate utf8mb3_tolower_ci) AS `CONSTRAINT_NAME`,`mysql`.`fkcu`.`ordinal_position` AS `ORDINAL_POSITION`,`mysql`.`fkcu`.`ordinal_position` AS `POSITION_IN_UNIQUE_CONSTRAINT`,`mysql`.`fk`.`referenced_table_schema` AS `REFERENCED_TABLE_SCHEMA`,`mysql`.`fk`.`referenced_table_name` AS `REFERENCED_TABLE_NAME`,`mysql`.`fkcu`.`referenced_column_name` AS `REFERENCED_COLUMN_NAME`,`mysql`.`fkcu`.`column_id` AS `column_id`,false AS `HIDDEN` from `mysql`.`foreign_keys` `fk` join `mysql`.`foreign_key_column_usage` `fkcu` where ((`mysql`.`fkcu`.`foreign_key_id` = `mysql`.`fk`.`id`) and (`mysql`.`fk`.`table_id` = `mysql`.`tbl`.`id`))) `constraints` join `mysql`.`columns` `col` join `mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` join lateral (/* select#7 */ select `mysql`.`idx`.`name` AS `CONSTRAINT_NAME`,if((`mysql`.`idx`.`type` = 'PRIMARY'),'PRIMARY KEY',`mysql`.`idx`.`type`) AS `CONSTRAINT_TYPE`,'YES' AS `ENFORCED` from `mysql`.`indexes` `idx` where ((`mysql`.`idx`.`table_id` = `mysql`.`tbl`.`id`) and (`mysql`.`idx`.`type` in ('PRIMARY','UNIQUE')) and (0 <> is_visible_dd_object(`mysql`.`tbl`.`hidden`,`mysql`.`idx`.`hidden`,`mysql`.`idx`.`options`)) and (if((`mysql`.`idx`.`type` = 'PRIMARY'),'PRIMARY KEY',`mysql`.`idx`.`type`) = 'FOREIGN KEY')) union all /* select#8 */ select (`mysql`.`fk`.`name` collate utf8mb3_tolower_ci) AS `CONSTRAINT_NAME`,'FOREIGN KEY' AS `CONSTRAINT_TYPE`,'YES' AS `ENFORCED` from `mysql`.`foreign_keys` `fk` where (`mysql`.`fk`.`table_id` = `mysql`.`tbl`.`id`) union all /* select#9 */ select `mysql`.`cc`.`name` AS `CONSTRAINT_NAME`,'CHECK' AS `CONSTRAINT_TYPE`,`mysql`.`cc`.`enforced` AS `ENFORCED` from `mysql`.`check_constraints` `cc` where false) `constraints` join `mysql`.`foreign_keys` `fk` join `mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` where ((`mysql`.`tbl`.`schema_id` = `mysql`.`sch`.`id`) and (`mysql`.`sch`.`catalog_id` = `mysql`.`cat`.`id`) and (`mysql`.`col`.`id` = `constraints`.`column_id`) and (`mysql`.`tbl`.`schema_id` = `mysql`.`sch`.`id`) and (`mysql`.`sch`.`catalog_id` = `mysql`.`cat`.`id`) and (`mysql`.`tbl`.`id` = `mysql`.`fk`.`table_id`) and (`mysql`.`fk`.`schema_id` = `mysql`.`sch`.`id`) and (`mysql`.`sch`.`catalog_id` = `mysql`.`cat`.`id`) and (`constraints`.`CONSTRAINT_NAME` = `constraints`.`CONSTRAINT_NAME`) and (`mysql`.`sch`.`name` = 'dev') and (`mysql`.`sch`.`name` = 'dev') and (`mysql`.`sch`.`name` = 'dev') and (`mysql`.`tbl`.`name` in ('atlas_schema_revisions','table_01','table_02','table_03','table_04','table_05','table_06','table_07','table_08','table_09','table_10','table_101','table_102','table_103','table_104','table_105','table_106','table_107','table_108','table_109','table_11','table_110','table_111','table_112','table_113','table_114','table_115','table_116','table_117','table_118','table_119','table_12','table_120','table_121','table_122','table_123','table_124','table_125','table_126','table_127','table_128','table_129','table_13','table_130','table_131','table_132','table_133','table_134','table_135','table_136','table_137','table_138','table_139','table_14','table_140','table_141','table_142','table_143','table_144','table_145','table_146','table_147','table_148','table_149','table_15','table_150','table_151','table_152','table_153','table_154','table_155','table_156','table_157','table_158','table_159','table_16','table_160','table_161','table_162','table_163','table_164','table_165','table_166','table_167','table_168','table_169','table_17','table_170','table_171','table_172','table_173','table_174','table_175','table_176','table_177','table_178','table_179','table_18','table_180','table_181','table_182','table_183','table_184','table_185','table_186','table_187','table_188','table_189','table_19','table_190','table_191','table_192','table_193','table_194','table_195','table_196','table_197','table_198','table_199','table_20','table_201','table_202','table_203','table_204','table_205','table_206','table_207','table_208','table_209','table_21','table_210','table_211','table_212','table_213','table_214','table_215','table_216','table_217','table_218','table_219','table_22','table_220','table_221','table_222','table_223','table_224','table_225','table_226','table_227','table_228','table_229','table_23','table_230','table_231','table_232','table_233','table_234','table_235','table_236','table_237','table_238','table_239','table_24','table_240','table_241','table_242','table_243','table_244','table_245','table_246','table_247','table_248','table_249','table_25','table_250','table_251','table_252','table_253','table_254','table_255','table_256','table_257','table_258','table_259','table_26','table_260','table_261','table_262','table_263','table_264','table_265','table_266','table_267','table_268','table_269','table_27','table_270','table_271','table_272','table_273','table_274','table_275','table_276','table_277','table_278','table_279','table_28','table_280','table_281','table_282','table_283','table_284','table_285','table_286','table_287','table_288','table_289','table_29','table_290','table_291','table_292','table_293','table_294','table_295','table_296','table_297','table_298','table_299','table_30','table_301','table_302','table_303','table_304','table_305','table_306','table_307','table_308','table_309','table_31','table_310','table_311','table_312','table_313','table_314','table_315','table_316','table_317','table_318','table_319','table_32','table_320','table_321','table_322','table_323','table_324','table_325','table_326','table_327','table_328','table_329','table_33','table_330','table_331','table_332','table_333','table_334','table_335','table_336','table_337','table_338','table_339','table_34','table_340','table_341','table_342','table_343','table_344','table_345','table_346','table_347','table_348','table_349','table_35','table_350','table_351','table_352','table_353','table_354','table_355','table_356','table_357','table_358','table_359','table_36','table_360','table_361','table_362','table_363','table_364','table_365','table_366','table_367','table_368','table_369','table_37','table_370','table_371','table_372','table_373','table_374','table_375','table_376','table_377','table_378','table_379','table_38','table_380','table_381','table_382','table_383','table_384','table_385','table_386','table_387','table_388','table_389','table_39','table_390','table_391','table_392','table_393','table_394','table_395','table_396','table_397','table_398','table_399','table_40','table_41','table_42','table_43','table_44','table_45','table_46','table_47','table_48','table_49','table_50','table_51','table_52','table_53','table_54','table_55','table_56','table_57','table_58','table_59','table_60','table_61','table_62','table_63','table_64','table_65','table_66','table_67','table_68','table_69','table_70','table_71','table_72','table_73','table_74','table_75','table_76','table_77','table_78','table_79','table_80','table_81','table_82','table_83','table_84','table_85','table_86','table_87','table_88','table_89','table_90','table_91','table_92','table_93','table_94','table_95','table_96','table_97','table_98','table_99')) and (`constraints`.`CONSTRAINT_NAME` = (`mysql`.`fk`.`name` collate utf8mb3_tolower_ci)) and (0 <> can_access_table(`mysql`.`sch`.`name`,`mysql`.`tbl`.`name`)) and (0 <> is_visible_dd_object(`mysql`.`tbl`.`hidden`)) and (0 <> can_access_table(`mysql`.`sch`.`name`,`mysql`.`tbl`.`name`)) and (0 <> is_visible_dd_object(`mysql`.`tbl`.`hidden`)) and (0 <> can_access_column(`mysql`.`sch`.`name`,`mysql`.`tbl`.`name`,`mysql`.`col`.`name`)) and (0 <> is_visible_dd_object(`mysql`.`tbl`.`hidden`,((`mysql`.`col`.`hidden` not in ('Visible','User')) or (0 <> `constraints`.`HIDDEN`)),`mysql`.`col`.`options`))) order by `constraints`.`CONSTRAINT_NAME`,`constraints`.`ORDINAL_POSITION` |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

jaugustin · 2023-06-08T12:32:35Z

here is the explain analyse if it can help

mysql> explain analyze SELECT 
    -> t1.CONSTRAINT_NAME, 
    -> t1.TABLE_NAME, 
'tab    -> t1.COLUMN_NAME,
    ->  t1.TABLE_SCHEMA, 
     -> t1.REFERENCED_TABLE_NAME, 
    -> t1.REFERENCED_COLUMN_NAME, 
    -> t1.REFERENCED_TABLE_SCHEMA, 
    -> t3.UPDATE_RULE, t3.DELETE_RULE 
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS t1 
    -> JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t2 
    -> JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS t3 ON t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME AND t1.CONSTRAINT_NAME = t3.CONSTRAINT_NAME AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA AND t1.TABLE_SCHEMA = t3.CONSTRAINT_SCHEMA 
    -> WHERE t2.CONSTRAINT_TYPE = 'FOREIGN KEY' AND t1.TABLE_SCHEMA = 'dev' AND t1.TABLE_NAME 
    -> IN ('atlas_schema_revisions', 'table_01', 'table_02', 'table_03', 'table_04', 'table_05', 'table_06', 'table_07', 'table_08', 'table_09', 'table_10', 'table_101', 'table_102', 'table_103', 'table_104', 'table_105', 'table_106', 'table_107', 'table_108', 'table_109', 'table_11', 'table_110', 'table_111', 'table_112', 'table_113', 'table_114', 'table_115', 'table_116', 'table_117', 'table_118', 'table_119', 'table_12', 'table_120', 'table_121', 'table_122', 'table_123', 'table_124', 'table_125', 'table_126', 'table_127', 'table_128', 'table_129', 'table_13', 'table_130', 'table_131', 'table_132', 'table_133', 'table_134', 'table_135', 'table_136', 'table_137', 'table_138', 'table_139', 'table_14', 'table_140', 'table_141', 'table_142', 'table_143', 'table_144', 'table_145', 'table_146', 'table_147', 'table_148', 'table_149', 'table_15', 'table_150', 'table_151', 'table_152', 'table_153', 'table_154', 'table_155', 'table_156', 'table_157', 'table_158', 'table_159', 'table_16', 'table_160', 'table_161', 'table_162', 'table_163', 'table_164', 'table_165', 'table_166', 'table_167', 'table_168', 'table_169', 'table_17', 'table_170', 'table_171', 'table_172', 'table_173', 'table_174', 'table_175', 'table_176', 'table_177', 'table_178', 'table_179', 'table_18', 'table_180', 'table_181', 'table_182', 'table_183', 'table_184', 'table_185', 'table_186', 'table_187', 'table_188', 'table_189', 'table_19', 'table_190', 'table_191', 'table_192', 'table_193', 'table_194', 'table_195', 'table_196', 'table_197', 'table_198', 'table_199', 'table_20', 'table_201', 'table_202', 'table_203', 'table_204', 'table_205', 'table_206', 'table_207', 'table_208', 'table_209', 'table_21', 'table_210', 'table_211', 'table_212', 'table_213', 'table_214', 'table_215', 'table_216', 'table_217', 'table_218', 'table_219', 'table_22', 'table_220', 'table_221',
    -> 'table_222', 'table_223', 'table_224', 'table_225', 'table_226', 'table_227', 'table_228', 'table_229', 'table_23', 'table_230', 'table_231', 'table_232', 'table_233', 'table_234', 'table_235', 'table_236', 'table_237', 'table_238', 'table_239', 'table_24', 'table_240', 'table_241', 'table_242', 'table_243', 'table_244', 'table_245', 'table_246', 'table_247', 'table_248', 'table_249', 'table_25', 'table_250', 'table_251', 'table_252', 'table_253', 'table_254', 'table_255', 'table_256', 'table_257', 'table_258', 'table_259', 'table_26', 'table_260', 'table_261', 'table_262', 'table_263', 'table_264', 'table_265', 'table_266', 'table_267', 'table_268', 'table_269', 'table_27', 'table_270', 'table_271', 'table_272', 'table_273', 'table_274', 'table_275', 'table_276', 'table_277', 'table_278', 'table_279', 'table_28', 'table_280', 'table_281', 'table_282', 'table_283', 'table_284', 'table_285', 'table_286', 'table_287', 'table_288', 'table_289', 'table_29', 'table_290', 'table_291', 'table_292', 'table_293', 'table_294', 'table_295', 'table_296', 'table_297', 'table_298', 'table_299', 'table_30', 'table_301', 'table_302', 'table_303', 'table_304', 'table_305', 'table_306', 'table_307', 'table_308', 'table_309', 'table_31', 'table_310', 'table_311', 'table_312', 'table_313', 'table_314', 'table_315', 'table_316', 'table_317', 'table_318', 'table_319', 'table_32', 'table_320', 'table_321', 'table_322', 'table_323', 'table_324', 'table_325', 'table_326', 'table_327', 'table_328', 'table_329', 'table_33', 'table_330', 'table_331', 'table_332', 'table_333', 'table_334', 'table_335', 'table_336', 'table_337', 'table_338',
    -> 'table_339', 'table_34', 'table_340', 'table_341', 'table_342', 'table_343', 'table_344', 'table_345', 'table_346', 'table_347', 'table_348', 'table_349', 'table_35', 'table_350', 'table_351', 'table_352', 'table_353', 'table_354', 'table_355', 'table_356', 'table_357', 'table_358', 'table_359', 'table_36', 'table_360', 'table_361', 'table_362', 'table_363', 'table_364', 'table_365', 'table_366', 'table_367', 'table_368', 'table_369', 'table_37', 'table_370', 'table_371', 'table_372', 'table_373', 'table_374', 'table_375', 'table_376', 'table_377', 'table_378', 'table_379', 'table_38', 'table_380', 'table_381', 'table_382', 'table_383', 'table_384', 'table_385', 'table_386', 'table_387', 'table_388', 'table_389', 'table_39', 'table_390', 'table_391', 'table_392', 'table_393', 'table_394', 'table_395', 'table_396', 'table_397', 'table_398', 'table_399', 'table_40', 'table_41', 'table_42', 'table_43', 'table_44', 'table_45', 'table_46', 'table_47', 'table_48', 'table_49', 'table_50', 'table_51', 'table_52', 'table_53', 'table_54', 'table_55', 'table_56', 'table_57', 'table_58', 'table_59', 'table_60', 'table_61', 'table_62', 'table_63', 'table_64', 'table_65', 'table_66', 'table_67', 'table_68', 'table_69', 'table_70', 'table_71', 'table_72', 'table_73', 'table_74', 'table_75', 'table_76', 'table_77', 'table_78', 'table_79', 'table_80', 'table_81', 'table_82', 'table_83', 'table_84', 'table_85', 'table_86', 'table_87', 'table_88', 'table_89', 'table_90', 'table_91', 'table_92', 'table_93', 'table_94', 'table_95', 'table_96', 'table_97', 'table_98', 'table_99') ORDER BY t1.CONSTRAINT_NAME, t1.ORDINAL_POSITION;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: CONSTRAINT_NAME, ORDINAL_POSITION  (actual time=350616..350616 rows=792 loops=1)
    -> Stream results  (cost=21.3e+6 rows=58.1e+6) (actual time=873..350615 rows=792 loops=1)
        -> Nested loop inner join  (cost=21.3e+6 rows=58.1e+6) (actual time=873..350612 rows=792 loops=1)
            -> Nested loop inner join  (cost=8.14e+6 rows=14.8e+6) (actual time=871..348112 rows=314424 loops=1)
                -> Nested loop inner join  (cost=6.66e+6 rows=14.8e+6) (actual time=871..346828 rows=314424 loops=1)
                    -> Nested loop inner join  (cost=1.49e+6 rows=7.38e+6) (actual time=0.341..245173 rows=125e+6 loops=1)
                        -> Nested loop inner join  (cost=755706 rows=7.38e+6) (actual time=0.332..65024 rows=125e+6 loops=1)
                            -> Nested loop inner join  (cost=2197 rows=8646) (actual time=0.24..324 rows=157609 loops=1)
                                -> Nested loop inner join  (cost=35 rows=65.8) (actual time=0.156..1.63 rows=397 loops=1)
                                    -> Nested loop inner join  (cost=2.1 rows=1) (actual time=0.0718..0.0755 rows=1 loops=1)
                                        -> Nested loop inner join  (cost=1.75 rows=1) (actual time=0.0667..0.0701 rows=1 loops=1)
                                            -> Nested loop inner join  (cost=1.4 rows=1) (actual time=0.0604..0.0636 rows=1 loops=1)
                                                -> Inner hash join (no condition)  (cost=1.05 rows=1) (actual time=0.0468..0.0495 rows=1 loops=1)
                                                    -> Covering index scan on cat using name  (cost=0.35 rows=1) (actual time=0.0032..0.00485 rows=1 loops=1)
                                                    -> Hash
                                                        -> Inner hash join (no condition)  (cost=0.7 rows=1) (actual time=0.0362..0.0377 rows=1 loops=1)
                                                            -> Covering index scan on cat using name  (cost=0.35 rows=1) (actual time=0.00456..0.00549 rows=1 loops=1)
                                                            -> Hash
                                                                -> Covering index scan on cat using name  (cost=0.35 rows=1) (actual time=0.0197..0.0225 rows=1 loops=1)
                                                -> Single-row covering index lookup on sch using catalog_id (catalog_id=mysql.cat.id, name='dev')  (cost=0.35 rows=1) (actual time=0.0129..0.013 rows=1 loops=1)
                                            -> Single-row covering index lookup on sch using catalog_id (catalog_id=mysql.cat.id, name='dev')  (cost=0.35 rows=1) (actual time=0.00593..0.00595 rows=1 loops=1)
                                        -> Single-row covering index lookup on sch using catalog_id (catalog_id=mysql.cat.id, name='dev')  (cost=0.35 rows=1) (actual time=0.00476..0.00486 rows=1 loops=1)
                                    -> Invalidate materialized tables (row from tbl)  (cost=26.3 rows=65.8) (actual time=0.084..1.53 rows=397 loops=1)
                                        -> Filter: (mysql.tbl.`name` in ('atlas_schema_revisions','table_01','table_02','table_03','table_04','table_05','table_06','table_07','table_08','table_09','table_10','table_101','table_102','table_103','table_104','table_105','table_106','table_107','table_108','table_109','table_11','table_110','table_111','table_112','table_113','table_114','table_115','table_116','table_117','table_118','table_119','table_12','table_120','table_121','table_122','table_123','table_124','table_125','table_126','table_127','table_128','table_129','table_13','table_130','table_131','table_132','table_133','table_134','table_135','table_136','table_137','table_138','table_139','table_14','table_140','table_141','table_142','table_143','table_144','table_145','table_146','table_147','table_148','table_149','table_15','table_150','table_151','table_152','table_153','table_154','table_155','table_156','table_157','table_158','table_159','table_16','table_160','table_161','table_162','table_163','table_164','table_165','table_166','table_167','table_168','table_169','table_17','table_170','table_171','table_172','table_173','table_174','table_175','table_176','table_177','table_178','table_179','table_18','table_180','table_181','table_182','table_183','table_184','table_185','table_186','table_187','table_188','table_189','table_19','table_190','table_191','table_192','table_193','table_194','table_195','table_196','table_197','table_198','table_199','table_20','table_201','table_202','table_203','table_204','table_205','table_206','table_207','table_208','table_209','table_21','table_210','table_211','table_212','table_213','table_214','table_215','table_216','table_217','table_218','table_219','table_22','table_220','table_221','table_222','table_223','table_224','table_225','table_226','table_227','table_228','table_229','table_23','table_230','table_231','table_232','table_233','table_234','table_235','table_236','table_237','table_238','table_239','table_24','table_240','table_241','table_242','table_243','table_244','table_245','table_246','table_247','table_248','table_249','table_25','table_250','table_251','table_252','table_253','table_254','table_255','table_256','table_257','table_258','table_259','table_26','table_260','table_261','table_262','table_263','table_264','table_265','table_266','table_267','table_268','table_269','table_27','table_270','table_271','table_272','table_273','table_274','table_275','table_276','table_277','table_278','table_279','table_28','table_280','table_281','table_282','table_283','table_284','table_285','table_286','table_287','table_288','table_289','table_29','table_290','table_291','table_292','table_293','table_294','table_295','table_296','table_297','table_298','table_299','table_30','table_301','table_302','table_303','table_304','table_305','table_306','table_307','table_308','table_309','table_31','table_310','table_311','table_312','table_313','table_314','table_315','table_316','table_317','table_318','table_319','table_32','table_320','table_321','table_322','table_323','table_324','table_325','table_326','table_327','table_328','table_329','table_33','table_330','table_331','table_332','table_333','table_334','table_335','table_336','table_337','table_338','table_339','table_34','table_340','table_341','table_342','table_343','table_344','table_345','table_346','table_347','table_348','table_349','table_35','table_350','table_351','table_352','table_353','table_354','table_355','table_356','table_357','table_358','table_359','table_36','table_360','table_361','table_362','table_363','table_364','table_365','table_366','table_367','table_368','table_369','table_37','table_370','table_371','table_372','table_373','table_374','table_375','table_376','table_377','table_378','table_379','table_38','table_380','table_381','table_382','table_383','table_384','table_385','table_386','table_387','table_388','table_389','table_39','table_390','table_391','table_392','table_393','table_394','table_395','table_396','table_397','table_398','table_399','table_40','table_41','table_42','table_43','table_44','table_45','table_46','table_47','table_48','table_49','table_50','table_51','table_52','table_53','table_54','table_55','table_56','table_57','table_58','table_59','table_60','table_61','table_62','table_63','table_64','table_65','table_66','table_67','table_68','table_69','table_70','table_71','table_72','table_73','table_74','table_75','table_76','table_77','table_78','table_79','table_80','table_81','table_82','table_83','table_84','table_85','table_86','table_87','table_88','table_89','table_90','table_91','table_92','table_93','table_94','table_95','table_96','table_97','table_98','table_99'))  (cost=26.3 rows=65.8) (actual time=0.0838..1.51 rows=397 loops=1)
                                            -> Index lookup on tbl using schema_id (schema_id=mysql.sch.id)  (cost=26.3 rows=132) (actual time=0.082..0.755 rows=397 loops=1)
                                -> Invalidate materialized tables (row from tbl)  (cost=19.9 rows=132) (actual time=0.0262..0.781 rows=397 loops=397)
                                    -> Filter: ((0 <> can_access_table(mysql.sch.`name`,mysql.tbl.`name`)) and (0 <> is_visible_dd_object(mysql.tbl.hidden)))  (cost=19.9 rows=132) (actual time=0.0261..0.761 rows=397 loops=397)
                                        -> Index lookup on tbl using schema_id (schema_id=mysql.sch.id)  (cost=19.9 rows=132) (actual time=0.0251..0.507 rows=397 loops=397)
                            -> Filter: (mysql.fk.schema_id = mysql.sch.id)  (cost=1.76 rows=854) (actual time=0.0338..0.374 rows=792 loops=157609)
                                -> Table scan on fk  (cost=1.76 rows=854) (actual time=0.0268..0.313 rows=843 loops=157609)
                        -> Filter: ((0 <> can_access_table(mysql.sch.`name`,mysql.tbl.`name`)) and (0 <> is_visible_dd_object(mysql.tbl.hidden)))  (cost=28.9e-6 rows=1) (actual time=0.00124..0.00131 rows=1 loops=125e+6)
                            -> Single-row index lookup on tbl using PRIMARY (id=mysql.fk.table_id)  (cost=28.9e-6 rows=1) (actual time=693e-6..715e-6 rows=1 loops=125e+6)
                    -> Filter: ((constraints.`CONSTRAINT_NAME` = (mysql.fk.`name` collate utf8mb3_tolower_ci)) and (constraints.`CONSTRAINT_NAME` is not null))  (cost=2.27..0.5 rows=2) (actual time=719e-6..720e-6 rows=0.00252 loops=125e+6)
                        -> Index lookup on constraints using <auto_key0> (CONSTRAINT_NAME=(mysql.fk.`name` collate utf8mb3_tolower_ci))  (cost=4.28..4.53 rows=2) (actual time=619e-6..619e-6 rows=0.00252 loops=125e+6)
                            -> Union all materialize (invalidate on row from tbl)  (cost=4.03..4.03 rows=6.59) (actual time=0.0402..0.0402 rows=8.02 loops=397)
                                -> Nested loop inner join  (cost=1.28 rows=3.52) (actual time=0.014..0.0167 rows=6.02 loops=397)
                                    -> Filter: (mysql.idx.`type` in ('PRIMARY','UNIQUE'))  (cost=0.686 rows=0.947) (actual time=0.00908..0.00956 rows=1 loops=397)
                                        -> Index lookup on idx using table_id (table_id=mysql.tbl.id)  (cost=0.686 rows=2.37) (actual time=0.0078..0.00855 rows=2.99 loops=397)
                                    -> Covering index lookup on icu using index_id_2 (index_id=mysql.idx.id)  (cost=0.646 rows=3.72) (actual time=0.00426..0.00604 rows=6.02 loops=397)
                                -> Nested loop inner join  (cost=2.1 rows=3.07) (actual time=0.00851..0.0117 rows=1.99 loops=397)
                                    -> Index lookup on fk using table_id (table_id=mysql.tbl.id)  (cost=1.05 rows=2.99) (actual time=0.00469..0.0053 rows=1.99 loops=397)
                                    -> Index lookup on fkcu using PRIMARY (foreign_key_id=mysql.fk.id)  (cost=0.284 rows=1.03) (actual time=0.00229..0.00281 rows=1 loops=792)
                -> Filter: ((0 <> can_access_column(mysql.sch.`name`,mysql.tbl.`name`,mysql.col.`name`)) and (0 <> is_visible_dd_object(mysql.tbl.hidden,((mysql.col.hidden not in ('Visible','User')) or (0 <> constraints.HIDDEN)),mysql.col.`options`)))  (cost=28.9e-6 rows=1) (actual time=0.00383..0.00391 rows=1 loops=314424)
                    -> Single-row index lookup on col using PRIMARY (id=constraints.column_id)  (cost=28.9e-6 rows=1) (actual time=0.00133..0.00135 rows=1 loops=314424)
            -> Index lookup on constraints using <auto_key0> (CONSTRAINT_NAME=constraints.`CONSTRAINT_NAME`)  (cost=1.89..2.14 rows=2) (actual time=0.00773..0.00774 rows=0.00252 loops=314424)
                -> Union all materialize (invalidate on row from tbl)  (cost=1.64..1.64 rows=3.93) (actual time=0.0139..0.0139 rows=1.99 loops=157212)
                    -> Filter: ((mysql.idx.`type` in ('PRIMARY','UNIQUE')) and (0 <> is_visible_dd_object(mysql.tbl.hidden,mysql.idx.hidden,mysql.idx.`options`)) and (if((mysql.idx.`type` = 'PRIMARY'),'PRIMARY KEY',mysql.idx.`type`) = 'FOREIGN KEY'))  (cost=0.686 rows=0.947) (actual time=0.00828..0.00828 rows=0 loops=157212)
                        -> Index lookup on idx using table_id (table_id=mysql.tbl.id)  (cost=0.686 rows=2.37) (actual time=0.00402..0.00711 rows=2.99 loops=157212)
                    -> Covering index lookup on fk using table_id (table_id=mysql.tbl.id)  (cost=0.561 rows=2.99) (actual time=0.00169..0.00237 rows=1.99 loops=157212)
                    -> Zero rows (Impossible WHERE)  (cost=0..0 rows=0) (actual time=43.1e-6..43.1e-6 rows=0 loops=157212)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 6 warnings (5 min 50.64 sec)

jaugustin · 2023-06-16T15:29:25Z

This is fixed on latest canary version thanks 👏

jaugustin changed the title ~~MySQL performace issue with lint command with some tables~~ MySQL performance issue with lint command with some tables Jun 8, 2023

jaugustin mentioned this issue Jun 13, 2023

Remove orderby to prevent performance issue in MySQL inspect queries #1740

Closed

a8m mentioned this issue Jun 16, 2023

sql/mysql: improve foreign-keys inspection query #1748

Merged

jaugustin closed this as completed Jun 16, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MySQL performance issue with lint command with some tables #1727

MySQL performance issue with lint command with some tables #1727

jaugustin commented Jun 8, 2023

jaugustin commented Jun 8, 2023

jaugustin commented Jun 8, 2023

jaugustin commented Jun 8, 2023

jaugustin commented Jun 16, 2023

MySQL performance issue with lint command with some tables #1727

MySQL performance issue with lint command with some tables #1727

Comments

jaugustin commented Jun 8, 2023

jaugustin commented Jun 8, 2023

jaugustin commented Jun 8, 2023

jaugustin commented Jun 8, 2023

jaugustin commented Jun 16, 2023