Every software engineer must have heard the name Mysql as it is one the most used and popular databases in world. When it comes to engineering there are whole lot of people who don’t even bother to think about database and focus only on code. This comes back and bite them bad when the system scales. Today we will see some points using which you can make sure you database will perform well. Lets see how to optimize MySQL database.
Suggested Books:
How to optimize MySQL database.
1. MySQL stores data on disk in contiguous blocks for fast access. So using datatypes which don’t have defined length make it hard for it to access fast.
Example: CHAR vs VARCHAR for fixed-length. InCHAR you define the length thus allows fast access while inVARCHAR it has to find the end of string thus making it slow.
2. Set the NOT NULL constraint where applicable. It will improve the search time. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column.
3. TEXT for large blocks of text such as blog posts. TEXT also allows for boolean searches. Using a TEXT field results in storing a pointer on disk that is used to locate the text block.
4. If you have to save images don’t save them as a blob in database. Instead save the object location in database and put it in file system.
5. Try to avoid expensive joins. If you have to join two table use where clause and join only the row you want instead of joining the whole database.
6. Columns that you use for querying (where clause columns) for operation (SELECT, GROUP BY, ORDER BY, JOIN) could be faster with indices.
Example: If you have table with columns name, age, year and you are using name and age in query try and index these two columns.
7. Indices are usually represented as self-balancing B-tree that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time. Creating index will keep data in memory and thus need more memory. But as you know memory reads are faster they will perform well instead of disk reads.
8. Writes will be slower with indices as indices need to be updated. When loading large amounts of data, it might be faster to disable indices, load the data, then rebuild the indices.
9. Yes bulk insertion and bulk deletion are far better as they save a lot of time in building indices. Always use these and you can notice the difference very easily.
10. You can also read about n+1 Query problem here.
So these are some steps that you may take to make your database perform better. There may be lot more points that can be added here. If have one please put it in comment and I will make sure to add it here.
