1

I stuck in a situation for using a conditional block statement: So I want to know which give fast

performance between mysql case expression and if-else condition.

My expression as follows

  1. Php condition block:

    $data=mysql_fetch_assoc(mysql_query("select action,state from my_table"));
if($data['action']==2 && $data['state']==0){
$state=1;
}
else{
$state=0;
}

  2. MySql condition :

    $data=mysql_fetch_assoc(mysql_query("select case when action = 2 and state = 0 then 1 else 0 end as state"));
$state =$data['state'];

I need a faster solution for it. So please tell me which is more recommended.

  • 1
    test your self, run each case and add this to get the time http://stackoverflow.com/questions/535020/tracking-the-script-execution-time-in-php#answer-9288945 – Emilio Gort Dec 21 '13 at 06:03
  • What do you mean by "faster"? You are not *doing anything* with the data so there is nothing to speed up, at least not on the PHP-side. You may be able to to squeeze out some speed on the database-side, but I doubt it will be worth your effort. – Sverri M. Olsen Dec 21 '13 at 09:03

2 Answers2

0

You can do some benchmark tests yourself to see which one performs best for you. However, in most cases, one won't see any differences in performance between 2 alternatives you provide.

Other things you should consider is code readability and maintainability. It is considered best practice to separate concerns into pieces. So, if your if-else condition is about business process, it should be in PHP. Otherwise, you can put it in data (mysql)

Andy Librian
  • 911
  • 5
  • 12
0

The PHP route will wind up being significantly faster. Regardless of how fast the MySQL engine can process the CASE statements internally, the API-overhead and resource usage will make the MySQL route definitively slower.

That being said there may be legitimate reasons to do the CASE in MySQL based on project design concerns.

virmaior
  • 424
  • 4
  • 14