Database optimisation

A few things to look at when optimising the database errors: 1. UPDATE using LIMIT when WHERE clause contains unique id. 2. If some often used stats need counting get them by incrementing some values instead of selecting via count. 3. Use normalization and indexing. 4. Use "INSERT DELAYED " for statistics 5. Log and alert for the Database errors $mysql_err = mysql_error($dblink); $mysql_err = mysql_errno($dblink); 6. Select the right engine:

                                                 MyISAM   InnoDB
----------------------------------------------------------------
Required full-text search                        Yes      5.6.4
----------------------------------------------------------------
Require transactions                             No       Yes
----------------------------------------------------------------
Frequent select queries                          Yes      No
----------------------------------------------------------------
Frequent insert, update, delete                  No       Yes
----------------------------------------------------------------
Row locking (multi processing on single table)   No       Yes
----------------------------------------------------------------
Relational base design                           No       Yes

7. When should I use ON DUPLICATE KEY EXISTS? INSERT IGNORE INTO `sendsms_unique_messages` (id, unique_id, record_date) VALUES (2, 'test2', NOW()), (1, 'test1', NOW()) ON DUPLICATE KEY UPDATE record_date = NOW() 8. Use INSERT IGNORE if some duplication is possible. 9. Keep in mind the NULL values in the searchable columns. 10. Use ENUM for fields like gender. It may be faster than VARCHAR when using JOIN. 11. Use where EXISTS to execute subquery only once. Issue description: http://dba.stackexchange.com/questions/23135/sub-query-executes-1-time-for-a-parent-query-or-more