xy-3aa
Last Updated: February 25, 2016
·
659
· gjerokrsteski
F8c4fdde8545731ac4a77b52e4896595

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
Say Thanks
Respond

2 Responses
Add your response

17643
Ffd2e222bbaceab39f8c12487627e1f0

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

over 1 year ago ·
17650
F8c4fdde8545731ac4a77b52e4896595

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

over 1 year ago ·