MySQL Bugs: #1010: Performance problem using PreparedStatement

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:	Windows (Win2k)
Assigned to:	Mark Matthews	CPU Architecture:	Any

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.

I have used Myisam tables and not InnoDB.

Changing the developer assigned. The driver is for java not MyODBC.

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.