#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,576
    Rep Power
    171

    What is the best way to test queires that may crash the database server?


    When dealing with multi-million row tables, there are times where I have to write queries that could return wrong results (due to wrong JOIN or bad sql practice).

    What would be the best way to test such queries before running them on the server? I see sometimes a wrong query can jam the whole database server. Even "EXPLAIN query" can jam the server.

    Does it make a difference to run the queries from out of phpmyadmin? For example in Putty?

    Anyways, tips would be very appreciated.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by zxcvbnm
    What would be the best way to test such queries before running them on the server?
    test them on your desktop computer, where you have a test database with the exact same structure as your live server, but fewer rows
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,576
    Rep Power
    171
    Originally Posted by r937
    test them on your desktop computer, where you have a test database with the exact same structure as your live server, but fewer rows
    Does it make difference if it is via phpmyadmin or command line?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by zxcvbnm
    Does it make difference if it is via phpmyadmin or command line?
    nope

    are you by any chance on a mac?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,576
    Rep Power
    171
    Originally Posted by r937
    nope

    are you by any chance on a mac?
    I have an imac and a mac book pro as well.

    Why?
    Last edited by zxcvbnm; July 5th, 2012 at 10:32 PM.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    because you're a musician and you're using phpmyadmin

    if you were on a pc, i'd recommend you switch to heidisql
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,576
    Rep Power
    171
    Originally Posted by r937
    because you're a musician and you're using phpmyadmin

    if you were on a pc, i'd recommend you switch to heidisql
    Logic studio and Reason (music things) are on Mac, phpmyadmin is here on this pc (Windows 7).

    I check heidisql.

    Thanks

IMN logo majestic logo threadwatch logo seochat tools logo