Mysqli query performance multi-query

Question

I was just hoping someone could help me speed up 4 queries with a multi query.

GOAL: a single multi query to function as the single queries below.

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.

Thank you in advance for your help.

current performance is around 1200ms. (+1000ms avg for facebook graph api query).

NOTE: af_freefeed.pageid & af_ban.pageid are both indexed in database.

ALSO: I have been studying and referencing from http://www.php.net/manual/en/mysqli.multi-query.php i just can not see how to get this config into multi with the if()

$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->query($q);
$rowtop = $readtop->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->query($q);
$rowf = $readf->fetch_assoc();
// increment views
$read = $fconn->query("Update af_freefeed SET views = views + 1 WHERE pageid = ".$banpage."");
}
$q=$fconn->query("SELECT pagename,views,pageid FROM af_freefeed ORDER BY views DESC LIMIT 0, 20");
unset($q);
unset($rowf);
unset($rowtop);
mysqli_close($fconn);

---

actual request times.

1. grah api: 1127.04610825ms.
2. conncect: 1.20711326599ms.
3. check banned: 0.405788421631ms.
4. get row: 418.189229965ms.
5. increment views: 472.24655151ms.
6. get top20: 94.31447983ms.

---

Multi_query #1 How to stop the multi query if user is banned?

---

Possible Contender: 943.8181ms. if added : 933.1279ms. if banned

1. 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.

2. replaced exit; with $thread_id = $fconn->thread_id; $fconn->kill($thread_id); if banned 953.4719ms. no gain.

---

$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->multi_query($query)) {
   if ($result = $fconn->store_result()) {
      while ($row = $result->fetch_row()) {
            print_r($row).'<br />';
      }
      $result->free();
   }
if ($fconn->more_results()) {
    while ($fconn->next_result()){ 
        if($thisresult = $fconn->store_result()){                   
            while (is_array($row = $thisresult->fetch_array())) {               
                if(isset($row['isbanned'])){
                    if($row['isbanned']===''.$banpage.''){
                $thread_id = $fconn->thread_id;
                $fconn->kill($thread_id);
                    // exit; 
                    }
                }
            }

        }
    }
}           
}

unset($query);
unset($result);
unset($thisresult);

---

Multi_query #2 "current for benchmark" How to remove duplicate fields in result set after next_result()?

---

2.667ms. / 1032.2499ms. but print_r is showing duplicate fields in $thisresults?

**Array
(
    [0] => 37
    [id] => 37
    [1] => 159616034235
    [pageid] => 159616034235
    [2] => 
    [userid] => 
    [3] => 30343
    [views] => 30343
    [4] => Walmart
    [pagename] => Walmart
)**

---

$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 '<pre>';
$i=0;
if ($fconn->multi_query($query)) {
   if ($result = $fconn->store_result()) {
        //$row = $result->fetch_assoc();    
      while ($row = $result->fetch_assoc()) {
        print_r($row).'<br />';
      }
      $result->free();
    }
if ($fconn->more_results()) {
    while ($fconn->next_result()){ 
        if($thisresult = $fconn->store_result()){       
             while ($row2 = $thisresult->fetch_array()) {
                if(isset($row2['isadded'])){
                    if($row2['isadded']===''.$banpage.''){
                        $addone = $fconn->query("Update af_freefeed SET views = views + 1 WHERE pageid = ".$banpage."");

                    }
                }   
                                print_r($row2); 
            }

        }
    }
}       

}
/* determine our thread id */
$thread_id = $fconn->thread_id;
/* Kill connection */
$fconn->kill($thread_id);
//

echo '</pre><hr/>';

---

Answer 1

Try to use index in getting count. Like for example COUNT(pageid). It will speed up your query.

Update

You can also try this link for further explanation

Answer 2

EDIT : So now, the conclusion: (test case below)

You cannot control the execution of subsequent statements of a multi-statement query.
You can therefore not use multi_query() in the way you wanted to.

Execute them all, or execute none.

---

Regarding
Multi_query #2 "current for benchmark" How to remove duplicate fields in result set after next_result()?

Use fetch_assoc() or fetch_array(MYSQLI_ASSOC) (both practically the same) instead of fetch_array().

---

About multi_query():

I recently worked on a program using the MySQL C API, which mysqli uses, too.
About multiple-statement query support the documentation states:

Executing a multiple-statement string can produce multiple result sets or row-count indicators. Processing these results involves a different approach than for the single-statement case: After handling the result from the first statement, it is necessary to check whether more results exist and process them in turn if so. To support multiple-result processing, the C API includes the mysql_more_results() and mysql_next_result() functions. These functions are used at the end of a loop that iterates as long as more results are available. Failure to process the result this way may result in a dropped connection to the server.

(emphasize added)

This leads to the conclusion, that aborting a multiple-statement query is not an intended feature.

Moreover, I didn't find any resource explaining when subsequent queries are actually executed.
Calling next_result() doesn't neccessarily mean that the query hasn't been executed already.

---

EDIT : TEST CASE

To prove what I previously assumed, I created a test case:

<?php
$db = new mysqli('localhost', 'root', '', 'common');
$query = 'SELECT NOW() as time;';
$query .= 'SELECT NOW() as time;';
$query .= 'SELECT NOW() as time;';
$query .= 'SELECT NOW() as time;';

if($db->multi_query($query)) {
    // Current time
    echo "'multi_query()' executed at:\n\t\t"
        .date('Y-m-d H:i:s')."\n";

    // First result
    if($result = $db->store_result()) {
        $i = 1;

        $row = $result->fetch_assoc();
        echo "'NOW()' of result $i:\n\t\t".$row['time']."\n";
        $result->free();
        // Wait 5 seconds
        sleep(5);

        // Subsequent results
        while($db->more_results() && $db->next_result()) {
            $result = $db->store_result();
            $row = $result->fetch_assoc();
            $i++;
            echo "'NOW()' of result $i:\n\t\t".$row['time']."\n";
            // Wait 5 seconds
            sleep(5);
            $result->free();
        }
    }
}
$db->close();
?>

This results in:

'multi_query()' executed at:
        2013-05-10 10:18:47
'NOW()' of result 1:
        2013-05-10 10:18:47
'NOW()' of result 2:
        2013-05-10 10:18:47
'NOW()' of result 3:
        2013-05-10 10:18:47
'NOW()' of result 4:
        2013-05-10 10:18:47

Given that, it is obvious that all four statements of the query were executed directly after the call to multi_query().
If they were only executed after calling next_result() there would be a 5 second delay caused by sleep(5) calls I added between the loop iterations.

Answer 3

Please run following query in mysql and check your query run time :

CREATE INDEX pagidIndex ON af_ban (pageid(11));
CREATE INDEX pagidFeedIndex ON af_freefeed (pageid(11));
CREATE INDEX viewsIndex ON af_freefeed (views(11));

Answer 4

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.

The following query may help you to update the view count. I assume that you already know the page_id.

UPDATE af_freefeed SET views=views+1 WHERE page_id=%s and page_id not in (select page_id from af_ban WHERE page_id=%s);

Answer 5

You could try something along these lines:

$sql = sprintf("SELECT af_freefeed.pageid FROM af_freefeed left join af_ban ".
               "on (af_freefeed.pageid = af_ban.pageid) ".
               "where af_freefeed.pageid = %s and ".
               "af_ban.pageid is null limit 1", $pageid);

to replace your first two queries.

The existence of a record in the results should indicate an unbanned user requesting the resource. Then you can do your update your views.

Hope this helps.