Bug #1010 Performance problem using PreparedStatement
Submitted: 7 Aug 2003 4:56 Modified: 21 Sep 2003 18:25
Reporter: Jan Van Santbrink Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.0 OS:Microsoft Windows (Win2k)
Assigned to: Mark Matthews CPU Architecture:Any

[7 Aug 2003 4:56] Jan Van Santbrink
Description:
I'm using MySQL 4.1.0 with jdbc mysql-connector-java-3.0.8-stable. When I execute a query that joins 8 tables
using PreparedStatement, ? and s.setString(1,"value") The resulting performance is 6500 msec. When I replace the
?'s in the query with the variables and switch off the setString's (which is not the intended use of PreparedStatement)
the performance is 180 msec, which is good (the result of the query is in all cases correct by the way).

Zooming into the problem is appears that the variable supplied to the join column in the query (there is in this
case one column that joins all tables) is the problem. When I use ? and s.setString(1,"xxx") explicitly for
each table/column combination of this column, like this:
...
AND xxx.col = yyy.col
AND xxx.col = zzz.col
AND xxx.col = ?
AND yyy.col = ?
AND zzz.col = ?
...
the resulting performance is 300 msec (but what is the use of joining in this case).
mysql-connector-java-3.0.7-stable gave the same results, so it seems like the database engine is doing this.

How to repeat:
Set up a query joining one table 10 times using different aliases and the table key to join these.
[8 Aug 2003 3:47] Jan Van Santbrink
I have used Myisam tables and not InnoDB.
[21 Sep 2003 17:13] Miguel Solorzano
Changing the developer assigned. The driver is for java not MyODBC.
[21 Sep 2003 18:25] Mark Matthews
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

We need a repeatable test case (schemas, data and query) that demonstrate the problem to track this down.