How can I optimize this?

[jvalarta]

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]

[wickning1]

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?

[jvalarta]

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]