|Subject:||Foreign_key_info very slow for a large database|
|Date:||Wed, 09 Sep 2009 14:05:03 +0100|
|To:||"bug-DBD-mysql " [...] rt.cpan.org|
|From:||Guoying Qi <gq1 [...] sanger.ac.uk>|
Hi, there. I am using Fey::ORM to create table classes from MySQL(V5.067). Fey::Loader needs read all the database schema out (get columns, primary and foreign keys) and create classes for each table. I have 41 tables in this schema. This works fine against our test database without much data inside. But when I run it against our live database (100G data in total) it becomes very slow. This is caused by calling foreign_key_info for each table. Each query takes around 15 seconds. The query DBD::mysql generated for one table is listed below. I am not sure why this query takes so long against the database with large data but very quick against the database with less data. I am not sure this is the problem from MySQL or there is other simple way to get foreign key information. If you can give me any suggestion, I will really appreciate it. Thank you very much! Guoying Qi SELECT NULL AS PKTABLE_CAT, A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM, A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, A.TABLE_CATALOG AS FKTABLE_CAT, A.TABLE_SCHEMA AS FKTABLE_SCHEM, A.TABLE_NAME AS FKTABLE_NAME, A.COLUMN_NAME AS FKCOLUMN_NAME, A.ORDINAL_POSITION AS KEY_SEQ, NULL AS UPDATE_RULE, NULL AS DELETE_RULE, A.CONSTRAINT_NAME AS FK_NAME, NULL AS PK_NAME, NULL AS DEFERABILITY, NULL AS UNIQUE_OR_PRIMARY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A, INFORMATION_SCHEMA.TABLE_CONSTRAINTS B WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND B.CONSTRAINT_TYPE IS NOT NULL AND A.REFERENCED_TABLE_SCHEMA = 'npgqcp' AND A.REFERENCED_TABLE_NAME = 'lane_qc' ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION -- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE.