查询中的PHP与MySQL性能(if，functions) [英] PHP vs MySQL Performance ( if , functions ) in query

问题描述

我刚刚看到了这个在这种情况下，我需要知道什么是最好的表现

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

如果查询中有陈述

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';
        }
     }
  ?>

从字符串中删除10

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 ) ;
     }
  ?>

等????? 和当我使用MYSQL函数或PHP函数吗?

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

推荐答案

以下是对您的问题的很好的描述: 在MySQL和PHP中进行计算

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 ) ;
 }

您要求您的服务器返回所有内容(这里我指的是评论的整个长度)，这可能很重要.乘以表中的行数可能会得出相当大的数据量，您必须在php和sql之间进行传输.在第二种情况下，此SELECT * , SUBSTR(comment, 0, 10) as min_comment FROM comments 这将在服务器上完成，并且不需要额外的内存.

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.

在第一个示例的情况下，我认为最好在sql端执行此操作，因为此后您仍然需要执行其他循环.除此之外，将要阅读您的代码的人可能会感到困惑，为什么您确实需要该代码.

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.

这篇关于查询中的PHP与MySQL性能(if，functions)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！