i hoping me speed 4 queries multi query.
goal: a single multi query function single queries below.
simple queries, checking 1 table see if user banned, if not, getting row id , updating it's view count 1. if user banned, not want last queries complete.
thank in advance help.
current performance around 1200ms. (+1000ms avg facebook graph api query).
note: af_freefeed.pageid & af_ban.pageid both indexed in database.
also: have been studying , referencing http://www.php.net/manual/en/mysqli.multi-query.php can not see how config multi if()
$fconn = new mysqli($fdbhost, $fdbuser, $fdbpass, $fdbname) or die ('error connecting mysqli'); // 12,000 rows af_ban - bigint(255) : indexed $q = sprintf('select count(pageid) numrowst af_ban pageid = %s', $banpage); $readtop = $fconn->query($q); $rowtop = $readtop->fetch_assoc(); // 1.17 million rows af_freefeed - bigint(255) : indexed if($rowtop[numrowst] == 0){ $q = sprintf('select count(pageid) numrowsf af_freefeed pageid = %s', $banpage); $readf = $fconn->query($q); $rowf = $readf->fetch_assoc(); // increment views $read = $fconn->query("update af_freefeed set views = views + 1 pageid = ".$banpage.""); } $q=$fconn->query("select pagename,views,pageid af_freefeed order views desc limit 0, 20"); unset($q); unset($rowf); unset($rowtop); mysqli_close($fconn); actual request times.
- grah api: 1127.04610825ms.
- conncect: 1.20711326599ms.
- check banned: 0.405788421631ms.
- get row: 418.189229965ms.
- increment views: 472.24655151ms.
- get top20: 94.31447983ms.
multi_query #1 how stop multi query if user banned?
possible contender: 943.8181ms. if added : 933.1279ms. if banned
10ms difference if exit loop banned. leads me believe loop completing queries before supposed executed, "next_result". or have error in how looped functions.
replaced
exit;$thread_id = $fconn->thread_id; $fconn->kill($thread_id);if banned 953.4719ms. no gain.
$banpage='234232874008'; $query = "select pagename,views,pageid af_freefeed order views desc limit 0, 2;"; $query .= "select pageid isbanned af_ban pageid = \"".$banpage."\";"; $query .= "select pageid isadded af_freefeed pageid = \"".$banpage."\";"; $query .= "update af_freefeed set views = views + 1 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 benchmark" how remove duplicate fields in result set after next_result()?
2.667ms. / 1032.2499ms. print_r 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 isbanned af_ban pageid = \"".$banpage."\";"; $query .= "select pageid isadded af_freefeed pageid = \"".$banpage."\";"; $query .= "select * af_freefeed order views desc limit 0, 20"; //$query .= "update af_freefeed set views = views + 1 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 pageid = ".$banpage.""); } } print_r($row2); } } } } } /* determine our thread id */ $thread_id = $fconn->thread_id; /* kill connection */ $fconn->kill($thread_id); // echo '</pre><hr/>';
edit : now, conclusion: (test case below)
you cannot control execution of subsequent statements of multi-statement query.
can therefore not use multi_query() in way wanted to.
execute them all, or execute none.
regarding
multi_query #2 "current benchmark" how remove duplicate fields in result set after next_result()?
use fetch_assoc() or fetch_array(mysqli_assoc) (both practically same) instead of fetch_array().
about multi_query():
i worked on program using mysql c api, mysqli uses, too.
about multiple-statement query support documentation states:
executing multiple-statement string can produce multiple result sets or row-count indicators. processing these results involves different approach single-statement case: after handling result first statement, it necessary check whether more results exist , process them in turn if so. support multiple-result processing, c api includes mysql_more_results() , mysql_next_result() functions. these functions used @ end of loop iterates long more results available. failure process result way may result in dropped connection server.
(emphasize added)
this leads conclusion, aborting multiple-statement query not intended feature.
moreover, didn't find any resource explaining when subsequent queries executed.
calling next_result() doesn't neccessarily mean query hasn't been executed already.
edit : test case
to prove assumed, created test case:
<?php $db = new mysqli('localhost', 'root', '', 'common'); $query = 'select now() time;'; $query .= 'select now() time;'; $query .= 'select now() time;'; $query .= 'select now() 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, obvious 4 statements of query executed directly after call multi_query().
if executed after calling next_result() there 5 second delay caused sleep(5) calls added between loop iterations.
Comments
Post a Comment