Shawn E Carter Shawn E Carter - 3 months ago 18
MySQL Question

Mysqli query performance multi-query

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

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.