Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-1180

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 3.1.2, 3.3.3
    • 3.4.1
    • MySQL compatibility
    • 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.

      Attachments

        Activity

          People

            diego dupin Diego Dupin
            ddtrong550 Trong Dinh
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.