SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 30 of 30
  1. #26
    SitePoint Addict bronze trophy vectorialpx's Avatar
    Join Date
    Dec 2012
    Location
    Bucharest
    Posts
    247
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    So now set that up so that several copies of that code run simultaneously so that at least two of them run the SELECT before the first one runs the INSERT.
    Why me? You're the one that thinks it's not a good solution.
    We're talking about "george" and "george" registering in the exact same time, in the time counted by a SELECT on the PrimaryKey, that may take 0.001 seconds.

    Quote Originally Posted by Lemon Juice View Post
    Let's not try to be so paranoid and watertight where not really required.
    Exactly!
    Be nice to nerds. Chances are you'll end up working for one - Bill Gates
    > photos | admin panel

  2. #27
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,863
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Lemon Juice View Post
    Let's not try to be so paranoid and watertight where not really required. What are the chances of two different people entering the same username and pressing the submit button within the same fraction of a second?
    If it is going to hang the entire database if they do then even the slightest chance should be avoided where possible - which it is simply by getting rid of the unnecessary select and testing the code returned from the insert. This also has the added benefit of making the database access far more efficient and so speeding up the processing.

    Getting rid of the select is worth it just from the viewpoint of the speed increase.

    Anyone who has done even an introductory course on using databases knows NEVER to use a SELECT before an INSERT because it is never needed, slows the processing down unnecessarily, and can potentially cause problems. Even disregarding the potential problems why slow your code down unnecessarily.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #28
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    There's a few things the OP hasn't mentioned:

    @Godz06 ;

    * What database server software is being used?
    * What storage engine is being used for the user table (not all of them support transactions)?
    * Is there a separate table for signups (confirmed ones being moved to the users table and unconfirmed ones deleted after so many days (or hours))?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #29
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    If it is going to hang the entire database if they do then even the slightest chance should be avoided where possible - which it is simply by getting rid of the unnecessary select and testing the code returned from the insert. This also has the added benefit of making the database access far more efficient and so speeding up the processing.

    Getting rid of the select is worth it just from the viewpoint of the speed increase.

    Anyone who has done even an introductory course on using databases knows NEVER to use a SELECT before an INSERT because it is never needed, slows the processing down unnecessarily, and can potentially cause problems. Even disregarding the potential problems why slow your code down unnecessarily.
    Generally, your points are valid but in this case you take wrong assumptions:

    1) Hanging the db - the db is not going to hang if the very unlikely scenario happens, the insert will just fail with an error and that's it. There's nothing going on here that would cause hanging of the db.

    2) Speed - even if you had 10000 new users in a day a simple select by a unique key would be minuscule performance-wise compared to all the other things the server would be doing to run the entire site that you wouldn't even notice the impact. This is worrying about micro-optimizations in places that are farthest from being a bottleneck.

    I'm not arguing that what you suggest is wrong. I'm just saying that using SELECT is not wrong either - in this particular case. There may be reasons to prefer the SELECT method because the requirements are not always so simple that you need to only check if a username exists. For example, you may want to check whether a username exists and whether it's active, or you need to grab some other properties of the existing user - then the unique index on the username is not enough to do it.

  5. #30
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,198
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by felgall View Post
    Anyone who has done even an introductory course on using databases knows NEVER to use a SELECT before an INSERT because it is never needed, slows the processing down unnecessarily, and can potentially cause problems. Even disregarding the potential problems why slow your code down unnecessarily.
    While theory tells us to do something a specific way, it is not always that pan out as the best option available.

    In regards to the actual select not being cost effective, that is only true when you run a smaller website with only one database server. When the website becomes popular enough to require a database cluster, it is cheaper hitting the read slaves than the masters.

    With that in mind, in this specific cause I would argue for that a select before the insert is the best solution (of course with the uniques in place as a final backup solution).


Bookmarks

Posting Permissions

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