MySQL Stored Procedure vs. complex query

Stored procedures will give you a small performance boost, but mostly they are for doing tasks that are difficult or impossible to do with a simple query. Stored procedures are great for simplifying access to data for many different types of clients. Database administrators love them because they control how the database is used as opposed to leaving those details to the developer.

Look to indexes and proper table design to get better performance.

Here is a nice description of your question: Doing calculations in MySQL vs PHP

In case of the second example the speed issue can be significant. First of all you do not know how big are your comments, so in case of

$x = mysql_query("SELECT * FROM comments");

 while( $res = mysql_fetch_assoc( $x ) ){
   $min_comment = substr( $x['comment'],0,10 ) ;
 }

you ask your server to return you everything (here I mean the whole length of the comment) and this can be significant. Multiplying by the number of rows in the table it can be quite big size of data, which you have to transfer between php and sql. In the second case this SELECT * , SUBSTR(comment, 0, 10) as min_comment FROM comments this will be already done on the server and will not require additional memory.

In case of the first example, I think it is also better to do it on sql side, because you will still need to do additional loop afterwards. Apart from this, people who will be reading your code might be confused why exactly do you need that code.

Possible character set issue? If your table character set is different from your database character set, this may be causing a problem.

See this bug report: http://bugs.mysql.com/bug.php?id=26224

[12 Nov 2007 21:32] Mark Kubacki Still no luck with 5.1.22_rc - keys are ingored, query takes within a procedure 36 seconds and outside 0.12s.

[12 Nov 2007 22:30] Mark Kubacki After having changed charsets to UTF-8 (especially for the two used), which is used for the connection anyways, keys are taken into account within the stored procedure!

The question I cannot answer is: Why does the optimizer treat charset conversions an other way within and outside stored procedures? (Indeed, I might be wrong asking this.)