PHP vs MySQL Performance ( if , functions ) in query

Question

I just see this artice

i need to know what's is best berformance in this cases

if statment in query

SELECT *,if( status = 1 , "active" ,"unactive") as status_val FROM comments

VS

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

     while( $res = mysql_fetch_assoc( $x ) ){
       if( $x['status'] == 1 ){
             $status_val = 'active';
        }else{
             $status_val = 'unactive';
        }
     }
  ?>

Cut 10 from string

SELECT * , SUBSTR(comment, 0, 10) as min_comment FROM comments

VS

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

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

etc ????? and When i use MYSQL functions or PHP functions ?

You can test it with microtime Yourself. But this is over-optimization and speed improvement will be very small... Most of the time You do not select big result sets from db — Juris Malinens, Nov 01 '12 at 11:57
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained and the [deprecation process](http://j.mp/Rj2iVR) has begun on it. See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — tereško, Nov 02 '12 at 02:30

score 4 · Answer 1 · answered Nov 01 '12 at 11:57

4

It depends. From a network point of view, in the first VS, PHP wins and in the second VS, MYSQL wins. That's because you send less data via socket. And thus the script becomes faster.

answered Nov 01 '12 at 11:57

ioan

751
1
9
26

score 2 · Accepted Answer · edited May 23 '17 at 11:53

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.

score 1 · Answer 3 · answered Nov 01 '12 at 11:57

In that case, using MySQL functions keeps you from looping in PHP and saves you a lot of code.

In other cases they have no alternative : for instance, when you use them in the WHERE part.

In terms of performance, the difference is trivial in most cases : use the simplest solution.

score 0 · Answer 4 · answered Nov 01 '12 at 11:57

0

Only answer: Measure it! You have two working solutions and want to achieve the best execution time.

answered Nov 01 '12 at 11:57

Sven

69,403
10
107
109

score 0 · Answer 5 · answered Nov 01 '12 at 11:57

0

I'd say it all depends on the systems on either end and current load. Typically DB servers are more stout than desktop machines and it would be faster to do the whole thing on the DB side rather than split it and use PHP partly.

answered Nov 01 '12 at 11:57

ethrbunny

10,379
9
69
131

PHP vs MySQL Performance ( if , functions ) in query

5 Answers5

Linked