Bug #100665 DatabaseMetaDataUsingInfoSchema#getImportedKeys query execution very slow

Submitted: 27 Aug 2020 22:18 Modified: 28 Aug 2020 5:49
Reporter: Micia Macier Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S5 (Performance)
Version:8.0.21 OS:Any
Assigned to: CPU Architecture:Any

[27 Aug 2020 22:18] Micia Macier
Description:
com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema#getImportedKeys generates a query that:
* returns duplicated entries
* query execution plan is suboptimal due to inner select on tables with many foreign keys

Provided local fix to the driver resulted in boosted execution speed by 200~700% (dependening on the table and the amount of references)

How to repeat:
1. invoke com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema#getImportedKeys for different tables 
2. check the query plan (= execution time) 

Suggested fix:
- use "SELECT DISTINCT" 
- replace INNER SELECT with JOIN
[27 Aug 2020 22:26] Micia Macier
Pull Request:
https://github.com/mysql/mysql-connector-j/pull/55
[28 Aug 2020 5:49] MySQL Verification Team
Hello Micia Macier,

Thank you for the report and feedback.
Imho this is duplicate of Bug #95280, please see Bug #95280

regards,
Umesh
[30 Aug 2020 22:12] Miron Balcerzak
Hi Umesh,
Yeah - it seems like reported issue is (partially) duplicating bug mentioned by you. Still there's a massive problem with performance of the query.

Kind regards,
Miron Balcerzak

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.