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
– Juris Malinens, Commented Nov 1, 2012 at 11:57
Please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — tereško
– tereško, Commented Nov 2, 2012 at 2:30

ioan · Accepted Answer · 2012-11-01 11:57:01Z

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 1, 2012 at 11:57

ioan

7711 gold badge9 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:53:25Z

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.

theredled · Accepted Answer · 2012-11-01 11:57:58Z

1

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.

answered Nov 1, 2012 at 11:57

theredled

1,07810 silver badges21 bronze badges

Comments

Sven · Accepted Answer · 2012-11-01 11:57:39Z

0

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

answered Nov 1, 2012 at 11:57

Sven

71.2k10 gold badges113 silver badges113 bronze badges

Comments

ethrbunny · Accepted Answer · 2012-11-01 11:57:59Z

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 1, 2012 at 11:57

ethrbunny

10.5k9 gold badges77 silver badges145 bronze badges

Collectives™ on Stack Overflow

PHP vs MySQL Performance ( if , functions ) in query

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related