Top 10 Best Practices in MySQL

MySQL is widely used around the world and is an open-source relational database management system. It has become popular due to its fast performance, high reliability, and ease of use. This article outlines some of the best practices in MySQL.

1. Always Use Appropriate Data Types

Use data types based on the nature of the data. Using inappropriate data types may take up more space or cause errors.

For example, using varchar(20) to store date-time values instead of the DATETIME datatype may lead to errors when updating time-related calculations and might result in invalid data storage.

2. Use CHAR(1) Instead of VARCHAR(1)

If the data is a single character string, use CHAR(1) instead of VARCHAR(1) because VARCHAR(1) will take additional bytes to store information.

3. Use CHAR Data Type for Fixed-Length Data

For example, using CHAR(1000) instead of VARCHAR(1000) will consume more space if the data length is less than 1000.

4. Avoid Using Regional Date Formats

When using DATETIME or DATE data types, always use the YYYY-MM-DD date format or the ISO standard date format compatible with your SQL engine. Other regional formats like DD-MM-YYYY, MM-DD-YYYY will not be stored correctly.

5. Set Indexes on Columns

Make sure that columns with indexes are used in JOIN statements to return query results quickly.

If you are using an UPDATE statement involving multiple tables, ensure that all columns used to join the tables are indexed.

6. Avoid Using Functions on Indexed Columns

Using functions on indexed columns does not take advantage of the index.

For example, if you want to fetch customer codes that start with “AK”, do not write:

SELECT columns FROM table WHERE left(customer_code, 2) = ‘AK’

Instead, rewrite it as follows:

SELECT columns FROM table WHERE customer_code LIKE ‘AK%’

This query will use the index for faster results.

7. Use SELECT * Only When Necessary

Do not write SELECT * in queries.

If there are many columns in the table, all of them will be returned, slowing down processing time.

Explicitly list only the columns you actually need.

8. Use ORDER BY Only When Necessary

Use ORDER BY when you want to display sorted results. Doing this in SQL can slow down the processing time in multi-user environments.

9. Choose the Right Database Engine

If you are developing an application that reads data more often than writing (e.g., a search engine), choose the MyISAM storage engine.

If you are developing an application that writes data more often than reading (e.g., real-time banking transactions), choose the InnoDB storage engine.

Choosing the wrong storage engine can affect performance.

10. Use EXISTS Wherever Necessary

If you want to check for data existence, do not use:

If (SELECT count(*) FROM Table WHERE col = ‘some value’) > 0

Instead, use EXISTS:

If EXISTS (SELECT * FROM Table WHERE col = ‘some value’)

This will process faster.

Source: https://bigdata-madesimple.com/top-10-best-practices-in-mysql/