title: Mysqli query performance multi-query
body: <p>I was just hoping someone could help me speed up 4 queries with a <strong>multi query</strong>.</p>
  
  <p><strong>GOAL:</strong> <em><code>a single multi query to function as the single queries below.</code></em> </p>
  
  <p>Simple queries, i am checking one table to see if user is banned, then if not, i am getting row for the id and updating it's view count by 1.  If user is banned, i do not want the last to queries to complete.</p>
  
  <p><strong><em>Thank you in advance for your help.</em></strong></p>
  
  <p>current performance is around <strong>1200ms.</strong> (+1000ms avg for facebook graph api query). </p>
  
  <p><strong><em>NOTE:</strong> af_freefeed.pageid &amp; af_ban.pageid are both indexed in database.</em></p>
  
  <p><strong>ALSO:</strong> I have been studying and referencing from <code>http://www.php.net/manual/en/mysqli.multi-query.php</code> i just can not see how to get this config into multi with the <code>if()</code></p>
  
  <pre><code>$fconn = new mysqli($fdbhost, $fdbuser, $fdbpass, $fdbname) or die  ('Error connecting to mysqli');
  // 12,000 rows for af_ban - bigint(255) : indexed
  $q = sprintf('SELECT COUNT(pageid) AS numrowst FROM af_ban WHERE pageid = %s', $banpage);
  $readtop = $fconn-&gt;query($q);
  $rowtop = $readtop-&gt;fetch_assoc();
  
  // 1.17 million rows for af_freefeed - bigint(255) : indexed
  if($rowtop[numrowst] == 0){
  $q = sprintf('SELECT COUNT(pageid) AS numrowsf FROM af_freefeed WHERE pageid = %s', $banpage);
  $readf = $fconn-&gt;query($q);
  $rowf = $readf-&gt;fetch_assoc();
  // increment views
  $read = $fconn-&gt;query("Update af_freefeed SET views = views + 1 WHERE pageid = ".$banpage."");
  }
  $q=$fconn-&gt;query("SELECT pagename,views,pageid FROM af_freefeed ORDER BY views DESC LIMIT 0, 20");
  unset($q);
  unset($rowf);
  unset($rowtop);
  mysqli_close($fconn);
  </code></pre>
  
  <hr>
  
  <p><strong>actual request times.</strong></p>
  
  <ol>
  <li>grah api: 1127.04610825ms.</li>
  <li>conncect: 1.20711326599ms.</li>
  <li>check banned: 0.405788421631ms.</li>
  <li>get row: 418.189229965ms.</li>
  <li>increment views: 472.24655151ms.</li>
  <li>get top20: 94.31447983ms.</li>
  </ol>
  
  <hr>
  
  <p><strong>Multi_query #1</strong> <em>How to stop the multi query if user is banned?</em> </p>
  
  <hr>
  
  <p><strong>Possible Contender:</strong>  <em>943.8181ms. if added</em> : <code>933.1279ms. if banned</code></p>
  
  <ol>
  <li><p>10ms difference if exit loop for banned. This leads me to believe the loop is completing all the queries before they are actually supposed to be executed, "next_result".  Or i have an error in how i looped the functions.</p></li>
  <li><p>replaced <code>exit;</code> with <code>$thread_id = $fconn-&gt;thread_id; $fconn-&gt;kill($thread_id);</code>
  if banned 953.4719ms. <strong>no gain</strong>. </p></li>
  </ol>
  
  <hr>
  
  <pre><code>$banpage='234232874008';
  $query  = "SELECT pagename,views,pageid FROM af_freefeed ORDER BY views DESC LIMIT 0, 2;";
  $query .= "SELECT pageid AS isbanned FROM af_ban WHERE pageid = \"".$banpage."\";";
  $query .= "SELECT pageid AS isadded FROM af_freefeed WHERE pageid = \"".$banpage."\";";
  $query .= "Update af_freefeed SET views = views + 1 WHERE pageid = \"".$banpage."\"";
  /* execute multi query */
  if ($fconn-&gt;multi_query($query)) {
     if ($result = $fconn-&gt;store_result()) {
        while ($row = $result-&gt;fetch_row()) {
              print_r($row).'&lt;br /&gt;';
        }
        $result-&gt;free();
     }
  if ($fconn-&gt;more_results()) {
      while ($fconn-&gt;next_result()){ 
          if($thisresult = $fconn-&gt;store_result()){                   
              while (is_array($row = $thisresult-&gt;fetch_array())) {               
                  if(isset($row['isbanned'])){
                      if($row['isbanned']===''.$banpage.''){
                  $thread_id = $fconn-&gt;thread_id;
                  $fconn-&gt;kill($thread_id);
                      // exit; 
                      }
                  }
              }
  
          }
      }
  }           
  }
  
  unset($query);
  unset($result);
  unset($thisresult);
  </code></pre>
  
  <hr>
  
  <p><strong>Multi_query #2</strong>  "current for benchmark" <em>How to remove duplicate fields in result set after next_result()?</em></p>
  
  <hr>
  
  <p><strong>2.667ms.</strong> / <strong>1032.2499ms.</strong> but print_r is showing duplicate fields in $thisresults?</p>
  
  <pre><code>**Array
  (
      [0] =&gt; 37
      [id] =&gt; 37
      [1] =&gt; 159616034235
      [pageid] =&gt; 159616034235
      [2] =&gt; 
      [userid] =&gt; 
      [3] =&gt; 30343
      [views] =&gt; 30343
      [4] =&gt; Walmart
      [pagename] =&gt; Walmart
  )**
  </code></pre>
  
  <hr>
  
  <pre><code>$query = "SELECT pageid AS isbanned FROM af_ban WHERE pageid = \"".$banpage."\";";
  $query .= "SELECT pageid AS isadded FROM af_freefeed WHERE pageid = \"".$banpage."\";";
  $query .= "SELECT * FROM af_freefeed ORDER BY views DESC LIMIT 0, 20";
  //$query .= "Update af_freefeed SET views = views + 1 WHERE pageid = \"".$banpage."\"";
  /* execute multi query */
  echo '&lt;pre&gt;';
  $i=0;
  if ($fconn-&gt;multi_query($query)) {
     if ($result = $fconn-&gt;store_result()) {
          //$row = $result-&gt;fetch_assoc();    
        while ($row = $result-&gt;fetch_assoc()) {
          print_r($row).'&lt;br /&gt;';
        }
        $result-&gt;free();
      }
  if ($fconn-&gt;more_results()) {
      while ($fconn-&gt;next_result()){ 
          if($thisresult = $fconn-&gt;store_result()){       
               while ($row2 = $thisresult-&gt;fetch_array()) {
                  if(isset($row2['isadded'])){
                      if($row2['isadded']===''.$banpage.''){
                          $addone = $fconn-&gt;query("Update af_freefeed SET views = views + 1 WHERE pageid = ".$banpage."");
  
                      }
                  }   
                                  print_r($row2); 
              }
  
          }
      }
  }       
  
  }
  /* determine our thread id */
  $thread_id = $fconn-&gt;thread_id;
  /* Kill connection */
  $fconn-&gt;kill($thread_id);
  //
  
  echo '&lt;/pre&gt;&lt;hr/&gt;';
  </code></pre>
  
  <hr>