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