MySQL Prepared Statements vs Stored Procedures Performance

Question

Hi there,

I have an old MySQL 4.1 database with a table that has a few millions rows and an old Java application that connects to this database and returns several thousand rows from this this table on a frequent basis via a simple SQL query (i.e. SELECT * FROM people WHERE first_name = 'Bob'. I think the Java application uses client side prepared statements but was looking at switching this to the server, and in the example mentioned the value for first_name will vary depending on what the user enters).

I would like to speed up performance on the select query and was wondering if I should switch to Prepared Statements or Stored Procedures. Is there a general rule of thumb of what is quicker/less resource intensive (or if a combination of both is better)

Answer 1

You do have an index of first_name, right? That will speed up your query a lot more than choosing between prepared statements and stored procedures.

Answer 2

If you have just one query to worry about, you should be able to implement the two alternatives (on your test platform of course!) and see which one gives you the best performance.

(My guess is that there won't be much difference though ...)

Answer 3

Looks like the best way is just to make the change and test it out in a test environment.

Thanks for the help.