Jump to content

Archived

This topic is now archived and is closed to further replies.

jvalarta

How can I optimize this?

Recommended Posts

I have hundreds of record updates coming in and the way Im doing it now is slowing down the users. Not good. This table has 1.7 million entries, and I have an index on the table.

Im wondering if there is a more efficient way to perform this query and update? (MYSQL 4.1.x)

[code]<?

// CHECK TO SEE IF AN ENTRY EXISTS
$query = "SELECT COUNT(*) AS RecordTotal FROM mytable WHERE ID = $ID && Name = '$Name'";
$result = mysql_query($query) or print (mysql_error());
   while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
     extract($row);
}

if (empty($RecordTotal)) { // IF NO ENTRY EXISTS, MAKE NEW ENTRY
     $sql = "INSERT INTO mytable (ID,Name,RecordCount) Values ($ID,'$Name',1)";
}

else { // IF ENTRY EXISTS, UPDATE RECORD COUNT
     $RecordCount++;
     $sql = "UPDATE mytable SET RecordCount=$RecordCount WHERE ID = $ID && Name = '$Name'";    
}

$result = mysql_query($sql);

?>[/code]

Share this post


Link to post
Share on other sites
Is ID a primary key? Auto-increment? Why are you querying both ID and Name? Isn't ID enough?

You say you have an index on the table, which columns are indexed? Are they indexed separately or as part of a multi-index?

Share this post


Link to post
Share on other sites
BINGO! I posted this in two places, and got the answer at the other, so I'll put it here in case anyone else runs across this.

The answer is, as of MySQL 4.1 there is a way to do an insert/update in one query. Here's how:

[code]$sql = "INSERT INTO mytable (ID,Name,RecordCount) VALUES ($ID,'$Name',1) ON DUPLICATE KEY UPDATE RecordCount = RecordCount + 1";
$result = mysql_query($sql);
[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.