Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
3.1.2, 3.3.3
-
None
-
Ubuntu 20.04 LTS
Description
- MariaDB connector 3.1.2.
- MySQL server 8.0.33+.
- Database has about 200 tables.
Issue: Performance query of getting foreign keys from Mariadb connector API.
Details : When using this mariadb connector with MySQL server 8.0.33, it takes time to return a foreign key.
Look at getExportedKeys() in org.mariadb.jdbc.DatabaseMetaData class , it's generated a SQL query:
SELECT KCU.REFERENCED_TABLE_SCHEMA PKTABLE_CAT, NULL PKTABLE_SCHEM, KCU.REFERENCED_TABLE_NAME PKTABLE_NAME, KCU.REFERENCED_COLUMN_NAME PKCOLUMN_NAME, KCU.TABLE_SCHEMA FKTABLE_CAT, NULL FKTABLE_SCHEM, KCU.TABLE_NAME FKTABLE_NAME, KCU.COLUMN_NAME FKCOLUMN_NAME, KCU.POSITION_IN_UNIQUE_CONSTRAINT KEY_SEQ, CASE update_rule WHEN 'RESTRICT' THEN 1 WHEN 'NO ACTION' THEN 3 WHEN 'CASCADE' THEN 0 WHEN 'SET NULL' THEN 2 WHEN 'SET DEFAULT' THEN 4 END UPDATE_RULE, CASE DELETE_RULE WHEN 'RESTRICT' THEN 1 WHEN 'NO ACTION' THEN 3 WHEN 'CASCADE' THEN 0 WHEN 'SET NULL' THEN 2 WHEN 'SET DEFAULT' THEN 4 END DELETE_RULE, RC.CONSTRAINT_NAME FK_NAME, RC.UNIQUE_CONSTRAINT_NAME PK_NAME,7 DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME WHERE KCU.REFERENCED_TABLE_NAME = 'memory_summary_by_host_by_event_name' ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, KEY_SEQ; |
Empty set (0.35 sec) |
Compare with MySQL connector lib, the query of MySQL lib returns quickly , it's about 0.03 sec.
We can see query execution time from Mariadb connector is x10 times slower than MySQL connector.
It's a performance issue when getting foreign key query, please help to see and double check if we can optimize the query to improve query performance.