Jump to content


Photo

How can I optimize this?


  • Please log in to reply
2 replies to this topic

#1 jvalarta

jvalarta
  • Members
  • PipPipPip
  • Advanced Member
  • 42 posts

Posted 09 March 2006 - 02:16 AM

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)

<? 

// 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); 

?>


#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 09 March 2006 - 05:22 AM

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?

#3 jvalarta

jvalarta
  • Members
  • PipPipPip
  • Advanced Member
  • 42 posts

Posted 09 March 2006 - 05:55 AM

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:

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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users