Last Updated: February 25, 2016
·
806
· gjerokrsteski

Some short and quick SQL performance tips

  • REPLACE

    • triggers foreign-key on DELETE = needs time
    • relatively long locks
    • much better -> concrete INSERT and UPDATE implementation
  • INSERT ON DUPLICATE KEY

    • expensive locking
    • at the past -> big ACID problems
    • better avoid it, if possible
  • UPDATE

    • updating more than one table at the same time -> expensive locking
    • update only the rows whose value must be changed: UPDATE users SET name="Billy Gatter" WHERE id=123 AND name != "Billy Gatter"
  • WHERE IN (???)

    • not more than 700 parameters
    • in a worst case -> better use multiple queries
  • ORDER BY

    • only if you really need it
    • otherwise ORDER BY NULL

2 Responses
Add your response

using EXPLAIN in MySQL is also very helpful e.g. if you need to know where to add indexes.

over 1 year ago ·

Yes, that is true. But onlly in MySQL. Thank you :-)

over 1 year ago ·