Good practices

Here's a small list of good MySQL practices. Use SQL_CALC_FOUND_ROWS when you create pagination related scripts:

SELECT SQL_CALC_FOUND_ROWS * FROM database.table LIMIT 10;
SELECT FOUND_ROWS();

Use UPDATE LIMIT when the number of the updated fields is knows. For example: update users by email where each user has unique email address. Use INSERT DELAYED for queries related to statistics and other that do not require 100% real time data. Use INSERT IGNORE to handle imports that may contain duplicated entries for unique keys. Use ON DUPLICATE KEY UPDATE to deal with inserts when duplicate entry is possible. In the example below column_b is column with a default value on update but we want to preserve the old value:

INSERT INTO hlr (column_a, column_b) VALUES 
('Value1a', 'Value1b'),
('Value2a', 'Value2b')
ON DUPLICATE KEY UPDATE 
column_a = 'Value1a', 
column_b = VALUES(column_b);

Some MySQL servers have some strict modes that may prevent some old code to be migrated. The long term solutions are to update the code or to set the sql_mode in the config but a quick temporary solution could be:

set GLOBAL sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

For a long term solution of the case above just add the following to /etc/mysql/my.conf:

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"