PHP Fastest way to insert/update row into MYSQL

PHP
1

Hi, guys!

I’m new there, I hope that will help me.

I use this poor way to make PHP to do what I want.

Fuction add/update information in mysql table, I use it to see, what users are doing in my website!

function addstats($action,$place){

   $specips = array();
   $specips[] = '127.10.0.1';
   
      $timenow = time();
      $myip = $_SERVER['REMOTE_ADDR'];
      $myreferral = $_SERVER['HTTP_REFERER'];
      
   if(!in_array($myip,$specips)){
      
      $query = "SELECT a_id FROM actions WHERE a_ip = '$myip' AND a_place = '$place' AND a_action = '$action'";
      $go = mysql_query($query);
      $rows = mysql_num_rows($go);
      $id = mysql_result($go,0,'a_id');

      if($rows == 1)
      {
      $query = "UPDATE actions SET a_time2='$timenow', a_referral2='$myreferral', a_clicks=a_clicks+1 WHERE a_id = $id";
      $go = mysql_query($query);
      }
      elseif($rows == 0)
      {
      $query = "INSERT INTO actions (a_ip, a_time1, a_time2, a_place, a_action, a_referral1, a_referral2, a_clicks)VALUES('$myip', '$timenow', '$timenow', '$place', '$action', '$myreferral', '$myreferral', '1')";
      $go = mysql_query($query);
      }
   }
}

But I need to that function works faster, because in the mysql table will be around ~100 000 rows and that slows down website, it is possible to make it much faster and smaller? :slight_smile:

I try to do that with “INSERT INTO actions … … ON DUPLICATE KEY UPDATE …”, but then there need to be more Primary keys, and all need to be Duplicate at the same time and then Update that row. :shifty:

I try to find it in Google, but not found that what I need… :frowning:

Sorry for bed English, I hope you Understand what I need and hope that will help me!

2

Ehh… Big thanks for:

ALTER TABLE actions
ADD UNIQUE ( a_ip, a_place, a_action )

All works good now! :wink: Thanks!

3

please include everything that you left out after the “…”

4

those aren’t three keys, those are three columns

if the combination of those columns should be unique, you need to define a UNIQUE key on them like this –

ALTER TABLE actions
ADD UNIQUE ( a_ip, a_place, a_action )

this produces one UNIQUE key, on three columns

5

That is how it works. If all those fields make up the key, it will issue an UPDATE when all 3 fields are matched.

6

Table
actions
Create Table
CREATE TABLE actions (
a_id int(10) unsigned…

And i know that with UNIQUE key it works, but not that what I want, It checks each KEY, and then if one key is duplicated, then it update row, but I need update row when only all keys is duplicated!

7

Hmm… It is possible to UPDATE row with “ON DUPLICATE KEY”, when all keys DUPLICATE, not 1 or 2, but only when all keys DUPLICATED, in this case all three (a_ip, a_place, a_action) ?

8

I’m not sure “ON DUPLICATE KEY UPDATE” is faster than separate queries. I use it for code simplicity alone. It’s certainly faster than REPLACE INTO though.

What exact problems are you having with ON DUPLICATE KEY UPDATE? Where a table has multiple fields as the primary key it works as you’d expect-- it updates when all key fields are matched, otherwise it inserts.

edit: In the example above, do you have an indexes on the fields you’re querying? a_ip, a_place and a_action. If not i’d suggest that is the source of any performance issues with the script you posted.

9

more primary keys? that’s not possible, because each table can have only one primary key, which is by definition unique, although it may consist of multiple columns

each table can have any number of additional UNIQUE keys as well, and ON DUPLICATE KEY checks all of them

please run this query and show the results:

SHOW CREATE TABLE actions