Resolved using replace into - is it an efficient option

[durangod]

Hi, i decided to do a member status indicator for my script, such as brb, on phone, away, that sort of thing and so i made a small table to hold the status.

It only has two fields, mem_id and status. i didnt originally have a primary key but after doing some reading on this option i figured i needed one so i made the mem_id the unique primary key.

I know there are concerns regarding server stress for lack of a better word when using this option on larger tables, but since this table is so small, im wondering if it is efficient to use this option.

The mod is built and it works, but i did want to ask because i trust the feedback here.

Here is my query.

PHP Code:


//update the status table
         $query = "REPLACE INTO member_status VALUES ('$userid', '$status')";
        $retvalu=mysql_query($query,$link) or die(mysql_error()); 


[Old Pedant]

It should be fine. Using INSERT...ON DUPLICATE KEY is probably slightly faster, but as you said: It's a small table. Yes, you *MUST* have a primary key. The docs point out that without one REPLACE is converted into a simple INSERT.

[Fou-Lu]

Old Pedant may know offhand as well; I believe that INSERT. . . ON DUPLICATE KEY actually issues an update (minus no key which makes sense of course), while the REPLACE issues a DELETE. . . INSERT. This is a big difference if you were to use enforced cascade delete foreign keys. For example a simple query of REPLACE INTO mytable (col1, col2) SELECT col1, col2 FROM mytable (I know its a terrible example); if mytable has cascade delete to mytable2, doing the above would effectively give you mytable in the exact same state as originally created, but mytable2 has now been truncated.

[Old Pedant]

Whoa, excellent point! I'd want to check the docs, but I'm pretty sure you are right.

CLEARLY, then, INSERT...ON DUPLICATE KEY is the better choice.

[durangod]

well i certainly dont want to truncate the table without intending to do so. Should i change the query on this?

[Old Pedant]

we both seem to think you should. Change to use INSERT...ON DUPLICATE KEY.

Should be better performance and won't affect any other tables with existing foreign key links.

[durangod]

Then that would be something like this.

PHP Code:


        //update the status table 
         $query = "INSERT INTO member_status(mem_id, status) 
                       VALUES ('$userid', '$status')
                       ON DUPLICATE KEY UPDATE status = '$status' "; 

        $retvalu=mysql_query($query,$link) or die(mysql_error()); 


[durangod]

worked great, thank you both very much

[abduraooft]

VALUES ('$userid', '$status')

You shouldn't wrap values to numeric columns by by quotes.

[durangod]

Originally Posted by abduraooft

You shouldn't wrap values to numeric columns by by quotes.

Thanks, i just thought that as long as that table field config was set to (int) it would be fine. Is that not the case?

[Fou-Lu]

MySQL is in loose mode by default. You can set it into strict mode which forces your datatypes to match. Its also a good habit to get into; I believe that MySQL is the only dbms that actually does implicit datatype conversions.
Using the wrong datatype means that MySQL has to convert it before using it. This has tremendous overhead and you can see it on batch inserts.

[durangod]

Thanks, i have to go thru the files anyway and fix 65 occurances of

PHP Code:

SELECT *, field, field, field 


to proper format, so ill do that at the same time and go thru and any int VALUES in the INSERT querys i will make sure they are without quotes at all...

thanks again.