I would like to share a part of some code written several years ago (I have changed from mysql to mysqli); this code works perfectly. I am new to my company, and our senior programmers wrote some extra lines to optimize branch predictions. My question is: is it necessary to follow this code? Does it improve performance?

We are hosting our files on Linux VPS CentOS 64Bit, 4GM RAM

 $gq = array();   //to hold global ques
 $gq = array_merge($m1_array,$m2_array,$m3_array,$m4_array,$m5_array,$mp5_array) ; 
        unset ($m1_array,$m2_array,$m3_array,$m4_array,$m5_array,$mp5_array);
    // collect answers
    //preparing list of question ids to get the answer 
    $qnolist ='';
    foreach ($gq as $qm)
            {$qnolist = $qnolist.','.$qm['qid'];}
    $qnolist=substr_replace($qnolist ,'',0,1); 
    //sorted list qry always good performance (branch predictor)
    $qnoarray =explode(",",$qnolist);
    $sql = "SELECT some fields where `qid` in ({$qnolist}) order by `qid`,`aid`;";
    $result = mysqli_query ($dbcon, $sql) or die("Server connection error..."); 
      $ga = array();
        while ($row = mysqli_fetch_assoc($result)) {
              array_push($ga, $row);

Performance tuning can be a strange thing sometimes. My instinct is to say that the explosion/implosion of the data will cost more than any branch-prediction benefits.

Also, the only use of the sorted in clause is in the database itself... which should be efficient regardless of the order of the values in the IN clause.

Finally, performance characteristics of big datasets can be very different to small data sets.

When people go out of their way to do a 'hack' for performance, it is normally only with a very good reason. Normally there is a 'good' comment as to why something is done.

In this case, I can't seem to find any reference on Google why a sorted in clause is beneficial.

So, you should do the following:

  1. identify how large the list normally are.
  2. benchmark the code as it is. focus first on the PHP. How much time is taken sorting the data for the in clause.
  3. benchmark the SQL query using in values that are randomly sorted, and compare it to queries with ordered values.

My instinct is that you do not have enough values in your data to make the processing worthwhile. But, this is also an occasion where there may be some odd, esoteric issue which is solved nicely.... Your only logical choices are:

  1. leave the code as is, and assume it does something good, or, at least does nothing bad
  2. benchmark the code yourself, and confirm the benefits and then fix it, or move on (satisfy your curiosity)
  3. speak with the programmer who added the code, and see if there is something you are missing.
  • 1
    \$\begingroup\$ thanks, I am preparing for benchmark ...let I update this post with result... \$\endgroup\$ – user1844933 Jan 11 '14 at 15:08
  • \$\begingroup\$ with out sorting query take some extra time, so I would like to choose "leave the code as is, and assume it does something good, or, at least does nothing bad" :) \$\endgroup\$ – user1844933 Jan 11 '14 at 15:59
  • 1
    \$\begingroup\$ the IN() clause in mysql is expensive/slow you're better off doing joins / group bys / distincts instead of doing an IN() and filtering/grouping in php outside of the actual query. I assume you have correctly built an index on the qid column too ? \$\endgroup\$ – Dave Jan 15 '14 at 12:53
  • \$\begingroup\$ @@dave thanks sir, yes all the columns in where clause are indexed... I agree "in" clause are expensive, but I don't have any alternative for using in clause, because of its random collection... please help me or give tip to use alternative for "in" clause for my situation ... \$\endgroup\$ – user1844933 Jan 26 '14 at 8:57

@rolfl's answer covers the major points very well. In addition, I would recommend that you change the comment, removing the phrase "branch predictor." A branch predictor is a part of the CPU which attempts to predict the result of a branch so that the CPU can speculatively execute the predicted branch. A programmer can write code that optimizes branch prediction in assembly, or C, or other compiled languages for which the programmer can reliably predict the machine code that the compiler will emit. In an interpreted language such as PHP or SQL, there is so much code between your source and the machine that optimizing for the branch predictor at the level of your PHP or SQL source no longer makes any sense.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.