0

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/>';
Improve this question
10
  • why not to measure these queries separately and find out which one slows down the whole bunch? Commented May 6, 2013 at 6:45
  • 1
    why do you think that a multi query will speed it up? Commented May 6, 2013 at 6:47
  • 3
    why don't you protect your queries from sql injections? Commented May 6, 2013 at 6:50
  • 1
    ty @YourCommonSense Facebook processes $banpage before i get it, it always returns object id, unless you see something in this one plugin i do not. all 620 lines of code look secure to me, but i am not smartest. Commented May 6, 2013 at 6:59
  • 2
    Why do you think using mysql_multi_query() will speed it up? The first thing to do is to identify what is taking too long to execute, then optimize that part. Commented May 9, 2013 at 16:26

5 Answers 5

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

why the minus 1? Please explain
where do you see minus 1 ?
thank you constatine, this shaved off about 400ms avg. I am sure the multi query will perform alot better, i am just not good with mysqli yet.
thank you again for the update constatine, but i am using mysqli, the mysql driver is being depreciated. i have been reading for days. php.net/manual/en/mysqli.multi-query.php
No problem. By the way I saw the minus 1 just before the plus 1's. Nevertheless, I'm glad it helped you out a little bit though.
3
+250

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.

Improve this answer

4 Comments

Would you mind explaining the downvote? We are all here to learn, so correct me.
@shawn-e-carter: I added a test case which proves that your intended use of multi_query() does not work.
wonderful, i wish i could have found this in the documents. executes on query, and will return duplicate results.
sir, you are truly and scholar and a gentleman. You have not only answered all my questions but you confirmed all of my guesses.
1

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));
Improve this answer

11 Comments

Sorry, I think there is problem in my SQL script.
What is your problem, it is query performance, right, only indexing you can do. if you want more optimization you should give your complete db schema and data access process.
turning the 4 queries into a multi query where i would see a gain if the page is banned.
Can you write stored procedure. you can do it all in single call, using stored procedure.
You can use stored procedure for multiple database if both are on same server. also you can use condition in stored procedure to manage the query which you want to run or not.
|
1

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);

Improve this answer

Comments

0

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.

Improve this answer

5 Comments

i used this with 2 single queries and in combo with another multi. No gain on total. I think my goal of a single mutli is never going to fly, i cannot stop or kill the threads before they run "incase of banned pages".
this smells like it's something wrong with the way the indexes on pageid are defined on one or both of the tables. It might be worth droping and rebuilding the pageid index on each of the tables.
ty @Orangepill i have dropped and rebuilt over and over, the issue i am having is stopping the multi query if the user is banned, it seems the last 2 queries always run, or i am not killing them in the right spot, or you just cannot interupt and kill a multiquery thread in myslqi. It is looking like i will have to run 2 multi queries, the second only running if user is not in banned table. I am going to use your build, with a multi behind a condition and see if there is noticeable gain.
anotherfeed.com/widgets/mysqli.php?set=approved *** 970.0301ms. that is just your query
if you could, i am having a problems returning the page id as an array with your query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.