20 Tips and Tricks Any MySQL Database Developer Should Consider

20 Tips and Tricks Any MySQL Database Developer Should Consider

1. Do not edit dump files - text editors can corrupt these files.

2. MyISAM block size - myisam_block_size has a default value of 1K which can be too small to be optimal. With an adequate block size, the OS will never have to write to they disc.

3. Turning on delay_key_write - Turning delay_key_write on ensures that the database will not flush the MyISAM key after every single write which can save time for future writes.

4. Joins: Create same column types and don't forget to index - Not using the same column type will result in MySQL trying to do a full table scan

5. Define WHERE clause with LIMIT 1 - When looking for one row, the query will stop when finding the match instead of the entire table.

6. Adding EXPLAIN keyword to SELECT queries - By adding the EXPLAIN keyword, MySQL will explain what your query is doing and store the results in a table.

7. Query Cache: Optimizing your queries - Query caching is usually enabled however CURDATE has this function disabled.

8. Use stack trace to isolate bugs - Follow Sky SQL's mini-tutorial on this

9. Enums, set SQL_MODE to traditional

10. MySQL changing root password to allow root acces can give you needed permission access

11. Database backup using mysqldump command

12. Fixing your config files using MySQL Tuner perl script

13. Identify slow queries - enable slow-query-log functionality to identify slow running queries and save results to a log file

14. Autoincrement: direct reset - sometimes the default settings are not needed

15. Partitioning MySQL tables - Use SHOW PLUGINS to determine if your version supports partitioning. Partitioning can help manage large tables of data

16. Enums and SQL - enums can be tricky when migrating

17. "Store IP Address as unsigned int" - storing IP addresses allows MySQL to store them as integer values to save money on storage costs

18. Use your indexes to create tables - An index can be created while the table itself is being created

19. Vertical partitioning - Can be used to structure your tables in a vertical manner

20. Storage engines matter - Beginners or novices should start in MyISAM - InnoDB tends to be more complicated