[SOLVED] optimizing code

[jakebur01]

This bit of code is bogging my server down like crazy. Is there any way I could manage this so it will sort things out better? There are a few thousand rows in this table.

mysql_query("UPDATE {$tablename} AS a, {$tablename} AS b 
SET a.$list =b.$list, a.$dealer=b.$dealer, a.$distributor=b.$distributor, a.$sort=b.$sort, a.$stdpack=b.$stdpack
WHERE LEFT(b.$part, CHAR_LENGTH(b.$part)-2)=a.$part AND RIGHT(b.$part, 2) IN ('-1', '-2', '-3', '-0')");


mysql_query("DELETE FROM {$tablename} WHERE $dealer IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')");
mysql_query("DELETE FROM {$tablename} WHERE $distributor IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')");
mysql_query("DELETE FROM {$tablename} WHERE $list IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')");

 

 

[premiso]

Make the columns $dealer, $distributor and $list indexes in the table structure. This will index them and should speed up the process.

 

As a side note, you can combine those three queries into using ($dealer IN()) OR ($distributor IN()) OR ....

[jakebur01]

Like this?

mysql_query("DELETE FROM {$tablename} WHERE $dealer IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$') or $distributor IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$') or $list IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')");

 

[premiso]

Yep give it a try and see for yourself.

 

Alternative, since you are using the same data more than once. Why not do this:

 

$inArray = array('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$');
$checkData = implode("', '", $inArray);

mysql_query("DELETE FROM {$tablename} WHERE $dealer IN({$checkData}) OR $distributor IN({$checkData}) OR $list IN({$checkData})");

 

A tad bit more user friendly/easier.

[jakebur01]

That worked great. But, I think i've singled out the code below as being the problem. The script runs in a blink when the code below is commented out.

mysql_query("UPDATE {$tablename} AS a, {$tablename} AS b 
SET a.$list =b.$list, a.$dealer=b.$dealer, a.$distributor=b.$distributor, a.$sort=b.$sort, a.$stdpack=b.$stdpack
WHERE LEFT(b.$part, CHAR_LENGTH(b.$part)-2)=a.$part AND RIGHT(b.$part, 2) IN ('-1', '-2', '-3', '-0')") or die(mysql_error());

 

 

[jakebur01]

Does anyone see anything wrong in this code?

I can't see any errors because the page will not even load due to this little block of code.

 

mysql_query("UPDATE {$tablename} AS a, {$tablename} AS b 
SET a.$list =b.$list, a.$dealer=b.$dealer, a.$distributor=b.$distributor, a.$sort=b.$sort, a.$stdpack=b.$stdpack
WHERE LEFT(b.$part, CHAR_LENGTH(b.$part)-2)=a.$part AND RIGHT(b.$part, 2) IN ('-1', '-2', '-3', '-0')") or die(mysql_error());

[jakebur01]

bump...