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
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