The Ultimate Guide to High Availability with MariaDB

MariaDB connector API get all foreign keys return slowly with MySQL server 8.0.33+

Environment:

- 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.

Answer Answered by Daniel Black in this comment.

I note this has a referenced table. If the schema (database) is passed as the second argument to getExportedKeys does this significantly improve the query time?

For clarity, also what is the MariaDB server version?

As far as I can see in the Connector/J implementation, its left to the server to implement this in a performing way.

Can you create a bug report on https://jira.mariadb.org - MDEV for the server or CONJ for Connector/J.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.