Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,486
    Thanks
    279
    Thanked 32 Times in 31 Posts

    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()); 
    Last edited by durangod; 10-11-2012 at 03:02 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,647
    Thanks
    80
    Thanked 4,636 Times in 4,598 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • Users who have thanked Fou-Lu for this post:

    durangod (10-11-2012)

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,647
    Thanks
    80
    Thanked 4,636 Times in 4,598 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,486
    Thanks
    279
    Thanked 32 Times in 31 Posts
    well i certainly dont want to truncate the table without intending to do so. Should i change the query on this?

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,647
    Thanks
    80
    Thanked 4,636 Times in 4,598 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    durangod (10-11-2012)

  • #7
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,486
    Thanks
    279
    Thanked 32 Times in 31 Posts
    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()); 
    Last edited by durangod; 10-11-2012 at 08:55 AM. Reason: removed single quotes from field names, oops lol

  • #8
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,486
    Thanks
    279
    Thanked 32 Times in 31 Posts
    worked great, thank you both very much

  • #9
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,865
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    VALUES ('$userid', '$status')
    You shouldn't wrap values to numeric columns by by quotes.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • Users who have thanked abduraooft for this post:

    durangod (10-11-2012)

  • #10
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,486
    Thanks
    279
    Thanked 32 Times in 31 Posts
    Quote Originally Posted by abduraooft View Post
    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?

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • #12
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,486
    Thanks
    279
    Thanked 32 Times in 31 Posts
    Thanks, i have to go thru the files anyway and fix 65 occurances of

    PHP Code:
    SELECT *, fieldfieldfield 
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •