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