[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

mysql update query

hello there,
usually the (server load) on one of my servers is at 1.00 or lower but when I run this php script it slows it down..
maybe because its trying to update 9500 records non-stop and using all resources? im not really an expert in this field
so maybe try and update query 10 or 20 and stop for 2 seconds or something.. what do you guys think would help?
EXPLAIN SELECT postid, pagetext FROM post WHERE ink_checked =0 ORDER BY postid ASC LIMIT 0 , 9500 
id 	select_type 	table 	type         possible_keys 	key          key_len         ref    rows 	Extra
1 	SIMPLE 	        post 	ref 	        ink_checked 	ink_checked   4            const   912666 	Using where; Using filesort
 
 
>>php script
 
<?php
session_start();
ignore_user_abort(false); 
set_time_limit(0); 
 
function GetMyConnection() {
    global $var;
    if( $var )
        return $var;
    $var = mysql_connect( 'localhost', 'root', 'pass') or die('Could not connect to server.' );
    mysql_select_db('db_forum', $var) or die('Could not select database.');
    return $var;
}
 
function cleanup($post) {
		$post = preg_replace('/\[url=([^\]]+)\][^\]]+\[\/url\]/i','\1',$post);
  return $post;
}
 
	$timestart = microtime(1); 
	$result = mysql_query("SELECT postid, pagetext FROM post WHERE ink_checked = 0 ORDER BY postid ASC LIMIT 0, 9500", GetMyConnection() );
 
	while($row = mysql_fetch_assoc($result))
	{
		$sID = $row['postid'];
		$post = cleanup($row['pagetext']);
		$post = mysql_real_escape_string($post);
		$sql = mysql_query("UPDATE post SET pagetext='$post', ink_checked='1' WHERE postid='$sID';", GetMyConnection() );
	}
?>

Open in new window

0
XK8ER
Asked:
XK8ER
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you show the explain plan for the SELECT, what about the explain plan for the UPDATE?
aka, is there an index on the field postid?
also, what is the data type of that field?

apart from that: you are opening a new connection for each update?... why not reuse the first connection?
<?php
session_start();
ignore_user_abort(false); 
set_time_limit(0); 
 
function GetMyConnection() {
    global $var;
    if( $var )
        return $var;
    $var = mysql_connect( 'localhost', 'root', 'pass') or die('Could not connect to server.' );
    mysql_select_db('db_forum', $var) or die('Could not select database.');
    return $var;
}
 
function cleanup($post) {
            $post = preg_replace('/\[url=([^\]]+)\][^\]]+\[\/url\]/i','\1',$post);
  return $post;
}
 
      $timestart = microtime(1); 
      $connection = GetMyConnection();
      $result = mysql_query("SELECT postid, pagetext FROM post WHERE ink_checked = 0 ORDER BY postid ASC LIMIT 0, 9500", $connection );
 
      while($row = mysql_fetch_assoc($result))
      {
            $sID = $row['postid'];
            $post = cleanup($row['pagetext']);
            $post = mysql_real_escape_string($post);
            $sql = mysql_query("UPDATE post SET pagetext='$post', ink_checked='1' WHERE postid='$sID';", $connection );
      }
?>

Open in new window

0
 
EMB01Commented:
I'm not sure if 9500 records would slow it down or not. It would depend on your server I suppose. Check out the following solution to a question I recently asked about processing speed and resource usage:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_24165151.html
0
 
racekCommented:

if you can use REPLACE or REGEXP it will be much faster with direct update ...
 
UPDATE post 
SET pagetext= REPLACE(REPLACE(pagetext,'/',''),'\',''),
    ink_checked='1';
 WHERE ink_checked = 0
LIMIT 9000;

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
XK8ERAuthor Commented:
>>is there an index on the field postid?

no it doesnt have an index.. but I can create an index for postid if that helps speed up things
postid        int(10)               UNSIGNED        No               auto_increment


>>what about the explain plan for the UPDATE?
this is what I get when I execute the explain update
SQL query:
 
EXPLAIN UPDATE post SET pagetext = 'test',
ink_checked = '1' WHERE postid = '3265'
 
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE post SET pagetext='test', ink_checked='1' WHERE postid='3265'' at line 1 

Open in new window

0
 
XK8ERAuthor Commented:
I did a test for only 50 records and it took 45 seconds to finish.. is that normal ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>but I can create an index for postid if that helps speed up things
please do so. should make the 50 records test drop to below 1-2 seconds

0
 
XK8ERAuthor Commented:
angelIII.. sounds good one thing before I add the index.. is it possible to add it while the database
is being use or its better to turn off the webserver while I do it..

ALTER TABLE post ADD INDEX (`postid `);
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, you can add the index any time.
0
 
XK8ERAuthor Commented:
The following indexes appear to be equal and one of them should be removed: PRIMARY, postid

PRIMARY         PRIMARY         8310          Edit          Drop          postid
postid         INDEX         8310          Edit          Drop          postid
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the second one is obsolete, due to the primary key which should be preferred.


did you actually check the suggestion of racek?
0
 
XK8ERAuthor Commented:
yes I did check racek suggestion but thats not what im looking for because my cleanup function is more complex
and has more things so the mysql REPLACE or REGEXP wont work..
0
 
XK8ERAuthor Commented:
how about we do something like I suggested in the first question.. to do like 10 or 20 update queries and stop for a few secs and then keep going?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
php does have a sleep function:
http://lu2.php.net/sleep
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now