There are a multitude of tips to optimize the performance of a MySQL database. This article focuses on a series of recommendations regarding the structure of a MySQL database.
The structure of the tables is a first point to work on, from the start of the design of the base. Here is a small list of recommendations:
- Do not use a BIGINT if a TINYINT is sufficient. Ditto for other types of data.
- To learn more: max values of numeric data in SQL
- Use VARCHAR over CHAR to store alphanumeric data of varying lengths. It saves memory space because CHAR has a fixed size, while VARCHAR has a variable size (does not work with UTF8)
- Latin1 is faster than UTF8 and UTF16
- Choose storage engines sparingly (MyIsam, InnoDB, etc.). If necessary it is possible to use different storage engines as needed.
Using indexes is essential to maintain good performance when reading data.
- Use indexes on the columns that need them
- Do not use indexes where they are not needed. You must check all the SQL queries one by one
- During a large data import, it is sometimes faster to remove an index and then put it back once the import is complete
Data is of course important. It is still necessary to master them because the weight of a database influences performance.
- Make sure to use TRIM () on alphanumeric data
- Avoid redundant data
- For columns containing predefined text values, it is best to use a numeric column that refers to the text. The correspondence can be done in the application using the database or via a correspondence table.
- Compress TEXT and BLOB data
- On a table that has a TEXT or BLOB column with other fields, it is sometimes preferable to separate the table in 2 to have the BLOB / TEXT on one side and the other columns on the other. This is valid specifically if the other data is often read while the data in the BLOB / TEXT is not.
Small base at the start, then …
When designing a small database, small optimizations don’t always seem essential. However, as a table becomes more and more imposing you have to look more and more closely at the small details. Increased monitoring is necessary to avoid loss of speed.
- Purge data that will never be used again
- Archive old data which is no longer used but which can be used later
- Perform tests regularly, as the table grows
- Test each change on a test environment
- With each big change, you have to go step by step. A small change on a huge table can have serious consequences
What else ?
If you have a few more tips that weren’t listed above, feel free to share them in the comments.