MYSQL Best Practises
![MYSQL Best Practises image 1](https://cdn-images-1.medium.com/max/800/1*Xryb1B1LpOgkMwNmznsaUw.jpeg)
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.
- https://stackoverflow.com/questions/8467092/does-using-limit-1-speed-up-a-query-on-a-primary-key
- https://stackoverflow.com/questions/455476/does-adding-limit-1-to-mysql-queries-make-them-faster-when-you-know-there-will#:~:text=The%20answer%2C%20in%20short%2C%20is,record%20that%20matches%20your%20query.
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