Speeding up query that updates multiple rows?

[predator]

My website has seen a great increase in traffic as of late, which has resulted in it becoming much slower, especially while logged in as admin and updating various kind of data. We can have up to 20-30 people logged in at the same time running update queries. The most slow pages are those that update multiple rows at one time, using loops, which I cannot think is the most ideal way, or? Does anyone one have a tip how I could change the query below.. so it is much more gentle/kind to the database? There can be up to 40 rows updated at once.

I have read a bit about using "CASE" inside SQL queries, but was unable to figure out how to apply it to my query below.

//CODE EXECUTED WHEN SOMEONE CLICKS THE UPDATE BUTTON

$playID = $_POST['playID'];
$kid = $_POST['kid'];
$GP2 = $_POST['GP2'];
$G2 = $_POST['G2'];
$A2 = $_POST['A2'];
$TP2 = $_POST['TP2'];
$PIM2 = $_POST['PIM2'];
$League2 = $_POST['League2'];

//LOOPING THROUGH EACH TABLE ROW AND UPDATING THE DATA
for($index = 0; $index < count($playID); $index++)
{

    $stats = "UPDATE stats 
                  SET League2 = '$League2[$index]', GP2 = '$GP2[$index]', 
                  G2 = '$G2[$index]', A2 = '$A2[$index]', 
                  TP2 = '$TP2[$index]', PIM2 = '$PIM2[$index]' 
                 WHERE 
                  PlayerID = $playID[$index] AND KID = $kid[$index]";

    $result2 = mysql_query($stats,$db_link);
}

[cahva]

First thing to check is that are you indexing your tables right. In this case atleast PlayerID and KID should be indexed. Other thing that will slightly slow down your app is when you use count($playID) in your for clause. It will execute that count in every iteration. So either set a $cnt var for that before the for loop or get rid of the for loop and replace it with foreach.

With foreach it would be like this:

foreach ($playID as $k=>$v)
{

$sql = sprintf("UPDATE stats
				SET League2 = '%s', GP2 = '%s', G2 = '%s', A2 = '%s', TP2 = '%s', PIM2 = '%s' 
				WHERE 
				PlayerID = %d AND KID = %d",
	$League2[$k],
	$GP2[$k],
	$G2[$k],
	$A2[$k],
	$TP2[$k],
	$PIM2[$k],
	$v,
	$kid[$k]
);

mysql_query($sql); 
}

If this data has to be updated, I dont see how CASE could help you in this case. As I dont know how your app works, I cannot give you any other optimization advice than to do some profiling on your page that you know what is causing slowdown. If its mysql related and your queries take long time, this would be seen in slowquery log. If youre on shared webhost you probably dont have access to that log so you have to ask your host about it.

[predator]

Thanks for the input! Will try the foreach option later.

Meanwhile, I know very little about indexes, tried to read a bit about it, but not 100% sure how to make of it. Anyway, pasting here some info that phpmyadmin gives me, not sure if you or someone else can figure out something by this?

INDEX

KEY TYPE CARDINALITY FIELD
PRIMARY PRIMARY 673455 KID
TeamID INDEX 7400 TeamID
PlayerID INDEX 96207 PlayerID
League INDEX 860 League
EndDate INDEX 139 EndDate
StartDate INDEX 138 StartDate

MEMORY USAGE
Data 40 731 KiB
Index 38 067 KiB
Totally 78 798 KiB

[Shrike]

A prepared statement could give a significant performance gain here. You will need to use mysqli (or a.n.other library) since php_mysql does not support them.

FYI adding indices does not speed up insert statements, it slows them down.

[cahva]

Shrike;10965657 wrote:

FYI adding indices does not speed up insert statements, it slows them down.

No **** sherlock 🙂 But indexing is very important otherwise as bad indexing can slowdown entire system even with only couple of thousand rows so thats more important.

@
When you say multiple people updating, is that the normal updating(couple of rows max) or did you mean that even normal user can update 40 rows per pageview?

[jgetner]

to speed up your site and database look at caching your results , prepared query's these 2 things should give you true increase in speed and query execution time along with overall sitespeed