Bug #61332 databaseMetadata.getColumns unncessarily slow when schema is has no wildcards

• Submitted: 27 May 2011 22:00
• Modified: 30 Jun 2011 14:59
• Reporter: David Morandi
• Status: Closed
• Category: Connector / J
• Severity: S5 (Performance)
• Version: 5.1.16
• OS: Any
• CPU Architecture: Any

[27 May 2011 22:00] David Morandi

Description:
On a database server with many schemas and many tables, databaseMetadata.getColumns can be slow (we've seen times around 1 second) even when schemaPattern and tablePattern are specified with no wildcards.  This appears to be because the function always uses a LIKE in the query against information_schema.columns even though there are no wilds card characters in the schemaPattern.  MySQL Server 5.1 introduced an optimization on information_schema queries to avoid scanning all schemas but using 'LIKE' rather than '=' in this query seems to prevent the optimization.   

How to repeat:
Call the getColumns function from java specifying a single schema and table name.  Caputure the resulting query (e.g. from the general log) and run an explain extended on the query.  It will say that it's scanning all schemas even though only a single schema should match. 

Suggested fix:
Check use an equal comparison rather than LIKE if the schemaPattern doesn't contain wild card characters.

[1 Jun 2011 6:17] Tonci Grgin

Hi David and thanks for your report.

From what I understand, you're saying optimizer in server should do a better job when presented with LIKE and exact values. You are also pleading for a workaround in c/J.

IMO, proper place to fix this would be an optimizer but I'll see what can be done in c/J providing I do not slow down the connector itself.

[30 Jun 2011 14:59] Tonci Grgin

Pushed up to revision 1065.