CodingForums.com - Resolved using replace into

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Resolved using replace into - is it an efficient option (http://www.codingforums.com/showthread.php?t=276030)

durangod

10-10-2012 04:36 PM

using replace into - is it an efficient option

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

10-10-2012 08:31 PM

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

10-10-2012 10:54 PM

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

10-10-2012 11:42 PM

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

10-11-2012 01:17 AM

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

Old Pedant

10-11-2012 01:29 AM

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

10-11-2012 03:08 AM

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

10-11-2012 02:01 PM

worked great, thank you both very much :)

abduraooft

10-11-2012 02:17 PM

Quote:

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

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

durangod

10-11-2012 02:31 PM

Quote:

Originally Posted by abduraooft (Post 1278776)

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

10-11-2012 02:53 PM

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

10-11-2012 03:00 PM

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.

All times are GMT +1. The time now is 03:44 PM.