#1
  1. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221

    Is INSERT SELECT a good idea or would it be a better option to do this with 2 queries


    Hi

    1 - Which columns would ideal to index (without slowing insert down on heavy traffic with millions of records)?

    2 - In general is INSERT SELECT prefered or 2 queries is a better approach?

    Thanks

    PHP Code:
    $conn = new PDO("mysql:host=$servername;dbname=$database"$username$password);
            
    $conn->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
            
    $stmt $conn->prepare("
                
    INSERT INTO launch_temp_leads 
                (user_id, 
                 launch_id, 
                 prospect_email, 
                 time_added, 
                 date_added, 
                 source) 
    SELECT launch_owners.id, 
           :launch_id, 
           :prospect_email, 
           :time_added, 
           :date_added, 
           :source 
    FROM   launch_owners 
           INNER JOIN launch_launches 
                   ON launch_launches.user_id = launch_owners.id 
                      AND launch_launches.id = :launch_id 
    WHERE  launch_owners.id = :id 
           AND secret_key = :secret_key "
    );

            
    $time date('H:i');
            
    $date date('Y-m-d');
            
    $stmt->bindParam(':id'$_POST['user_id']);
            
    $stmt->bindParam(':secret_key'$_POST['secret_key']);
            
    $stmt->bindParam(':launch_id'$_POST['launch_id']);
            
    $stmt->bindParam(':prospect_email'$_POST['prospect_email']);
            
    $stmt->bindParam(':time_added'$time);
            
    $stmt->bindParam(':date_added'$date);
            
    $stmt->bindParam(':source'$_POST['source']);
            
    $stmt->execute(); 
  2. #2
  3. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,862
    Rep Power
    9646
    1. Have you figured out what indexes do yet? If you understand what they mean then you should be able to figure out what columns are worth considering indexing. Can you tell me what they are?

    2. Why two queries? What kind of reason do you think would make sense for turning one query into two?
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    Originally Posted by English Breakfast Tea
    1 - Which columns would ideal to index
    columns used in searching/filtering, then columns used in grouping or sorting


    Originally Posted by English Breakfast Tea
    2 - In general is INSERT SELECT prefered
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221
    So adding a Unique index on "secret_key" should be ideal.

    Also about 2 vs 1: Becuase 2 queries are super simple. I am not sure about the complexity of INSERT SELECT.

    PHP Code:
    //Check Credentials
            
    $stmt $conn->prepare("SELECT secret_key, launch_timezone FROM launch_owners INNER JOIN launch_launches ON launch_launches.user_id = launch_owners.id WHERE launch_owners.id = :id AND secret_key = :secret_key AND launch_launches.id = :launch_id");
            
    $stmt->bindParam(':id'$_POST['user_id']);
            
    $stmt->bindParam(':secret_key'$_POST['secret_key']);
            
    $stmt->bindParam(':launch_id'$_POST['launch_id']);
            
    $stmt->execute();
            if(
    $stmt->rowCount()==1)
                {
                    
    $result $stmt->fetchAll();
                    
    date_default_timezone_set($result[0]['launch_timezone']);
                    
    //Insert record
                        
                        
    $stmt $conn->prepare("INSERT INTO launch_temp_leads (user_id, launch_id, prospect_email, time_added, date_added, source) VALUES (:user_id, :launch_id, :prospect_email, :time_added, :date_added, :source)");
                        
    $time date('H:i');
                        
    $date date('Y-m-d');
                        
    $stmt->bindParam(':user_id'$_POST['user_id']);
                        
    $stmt->bindParam(':launch_id'$_POST['launch_id']);
                        
    $stmt->bindParam(':prospect_email'$_POST['prospect_email']);
                        
    $stmt->bindParam(':time_added'$time);
                        
    $stmt->bindParam(':date_added'$date);
                        
    $stmt->bindParam(':source'$_POST['source']);
                        
    $stmt->execute();
                    
    //Insert record    
                

  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    Originally Posted by English Breakfast Tea
    Becuase 2 queries are super simple. I am not sure about the complexity of INSERT SELECT.
    you know what else is super simple? run a query, and then write a loop over the results, issuing a separate simple query for each row returned by the first

    super, super simple... should appeal to you... look Ma, no join!!

    as for your "not sure about the complexity" comment, i think you'd change your tune real fast if your INSERT SELECT were handling more than one row returned from the SELECT

    which happens for more often than your case here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221
    Originally Posted by r937
    you know what else is super simple? run a query, and then write a loop over the results, issuing a separate simple query for each row returned by the first

    super, super simple... should appeal to you... look Ma, no join!!
    I am not sure if you're being sarcastic or saying this is a good idea. Sometimes you have dry humour hard for none English speaker. Or maybe you're being serious.

    I tested up to 60k SIMPLE updates in a loop, it happens within 5 seconds. I've also jammed the server by writing bad queries. Not in a loop. Just 1 query. What I am saying, I've never seen server jam because of simple queries in a loop. I've seen server jam because of bad queries, even if not in a loop. And that's the whole reason I asked if INSERT SELECT is good or no. Is it fundamentally ok?

    Originally Posted by r937
    as for your "not sure about the complexity" comment, I think you'd change your tune real fast if your INSERT SELECT were handling more than one row returned from the SELECT which happens far more often than your case here
    "not sure about the complexity" comment I meant I don't know if it's a good idea or not. I didn't say it's complex or simple. Did you assume I said it's simple?
  12. #7
  13. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,862
    Rep Power
    9646
    Originally Posted by English Breakfast Tea
    So adding a Unique index on "secret_key" should be ideal.
    I would not.

    Which table is secret_key on? launch_owners? Your query is already using the table's id, which is unique, so MySQL will be able to find that row immediately. Adding a unique key on secret_key won't improve the performance but it would necessitate storing an index - one containing strings that aren't very short, and that (like all indexes) would have to be managed every time records are added or removed. Really the only purpose of that condition is for security: to verify that the secret_key matches the launch_owner and thus that the request is legitimate.

    UNIQUE indexes have another potential argument of enforcing uniqueness, however something like secret_key (a) will >99.9% be unique anyways and (b) it doesn't matter if two accounts accidentally have the same secret. It's the same argument that's used for passwords: it's quite okay for two people to have the same password.
    Last edited by requinix; June 19th, 2018 at 05:10 PM.
  14. #8
  15. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221
    Originally Posted by requinix
    I would not.

    Which table is secret_key on? launch_owners?
    Yes. Because Ontraport's webhook doesn't send authenticate headers so secret_key would be the next best thing.

    Is Rudy trying to say INSERT SELECT is a bad idea compared to 2 queries?
  16. #9
  17. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,862
    Rep Power
    9646
    Originally Posted by r937
    Originally Posted by English Breakfast Tea
    2 - In general is INSERT SELECT prefered
    yes
      
  18. #10
  19. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221
    Trying to understand. Why did he make the comment "I think you'd change your tune real fast".

    Normally makes comments like that if the approach is not good. I am already confused enough.
  20. #11
  21. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,271
    Rep Power
    4193
    A single INSERT ... SELECT query is much better than a SELECT query followed by n INSERT queries, where n is the number of results.

    For low values of n, the different is probably not measurable, but as n increases the difference can become drastic.

    Think about (roughly) how each option would work.

    INSERT ... SELCT is like:
    - "Hey, go find the data matching x conditions, then copy the results over to table y."

    SELECT + n INSERTs is like:
    - "Hey, find the data matching x conditions and give it to me"
    - (server hands you n pieces of paper).
    - (repeat n times)
    ---- "Hey, here's some data"
    ---- (give server one paper)
    ---- "Copy it into table y"

    The second option is much more work and involves an entirely unnecessary data transfer between the server and your script.
    Last edited by kicken; June 19th, 2018 at 10:09 PM.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  22. #12
  23. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,862
    Rep Power
    9646
    Analogies!

    Your neighbor was really nice to watch your dog that one weekend. You should thank them.
    INSERT...SELECT: Buy a thank you card and give it to your neighbor.
    SELECT+INSERT: Buy a thank you card, come over here, give it to me, and go back home. I look at it. Now come back here again, I give you the card back, and you give it to your neighbor.

IMN logo majestic logo threadwatch logo seochat tools logo