MYSQL Best Practises

MYSQL is one of the most popular open-source relational database management systems. Mysql best practises that every developer should know.

MYSQL Best Practises

MYSQL is one of the most popular open-source relational database management systems. It has worldwide popularity because provides fast performance and high reliability. In this article, I will try to inform you of some MYSQL best practices in my vision.

1- Use LIMIT 1 If you need a unique row

If you trying to get just one row, always prefer LIMIT 1 in your SELECT query.

If you don’t add this, the database engine will need to go through the whole index. If you just add a basic limit it will stop after finding the result.

Don’t use it like that;

SELECT * FROM ``users`` WHERE ``name`` = 'atakan';

Prefer this;

SELECT * FROM ``users`` WHERE ``name`` = 'atakan' LIMIT 1;

Note: It is not speeding up if the column is the primary key.

2- Don’t use SELECT *

Don’t add * to all your queries. It is affecting negatively your query performance. You must just get the columns that you need.

Don’t use it like that;

SELECT * FROM ``users`` WHERE ``username`` = 'test';

Prefer this;

SELECT id, name FROM ``users`` WHERE ``username`` = 'test' LIMIT 1;

3- Use EXPLAIN for SELECT queries;

You can basically use EXPLAIN keyword to understand what happens with your query. You can see which index is used by your query and it can be useful to find performance issues in your complex query.

Example:

EXPLAIN SELECT * FROM ``users`` WHERE ``username`` = 'test' LIMIT 1;

4- Use EXISTS for checking existing data

Generally, we use COUNT for checking existing data but we have an EXISTS function to do this operation and it has a better performance.

Don’t use it like that;

If (SELECT id from users WHERE username = 'test') > 0

Prefer this;

If EXISTS(SELECT id from users WHERE username = 'test')

5- Don’t use functions with indexed columns

If you use functions over the indexed columns, you just lose the purpose of the index. It will make a full table search and this is just a slow response time.

Don’t use it like that;

SELECT username FROM users WHERE left(username,2)='AT'

6- Use ENUM rather than VARCHAR

ENUM is faster than VARCHAR and also, in my opinion, it is better to understand the table and the purpose of the column.

7- Prefer NOT NULL If You Can

NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.

8- Use CHAR for fixed-length data

If you don’t have a fixed length of data, using varchar is worth more than using char. Because it will take more space.

9- Use CHAR (1) over VARCHAR (1)

For 1 character you can use CHAR instead of VARCHAR. It will take less space than a varchar(1).

10- Use Index for key columns

If you will make a JOIN, you need to index your key column. This will improve your querying performance.

11- Use ORDER BY If needed

If you don’t need an order, do not add a default order to your queries. It is just getting slow down your application.

12- Split Big Delete and Insert Queries

Big insert or delete queries can lock your tables and it can affect badly your website performance.

For applying big queries, just chunk them into small pieces and limit them. You can wait a little bit after every chunk.

13- Don’t break the query cache

If you are opened a query cache in your MYSQL server, most of the repeated queries can return fastly from the cache.

Don’t use it like that;

SELECT id FROM users WHERE created_at >= CURDATE()

Prefer this;

SELECT id FROM users WHERE created_at >= '2022-10-01'

Run a query with the static date and calculate the in your application layer, for example in PHP just use date(‘Y-m-d’).

References

MYSQL Best Practises