MySQL - Good Habits

In this article, we are going to learn few good habits that we can consider important aspect while work with MySQL to improve performance & troubleshoot as following below:

#1 - Do not use stored procedure & function parameters name same as WHERE clause field name
It will be responding with all record of query because MySQL interprets field value as parameter value similar like 1=1.

Example:

1. -- Bad  
2. CREATE PROCEDURE `getPersonById`(IN id INT(10))  
3. BEGIN  
4. -- return all record instead  
5. SELECT id,name FROM person WHERE id = id;  
6. END  
7. -- Good  
8. CREATE PROCEDURE getPersonById(IN personId INT(10))  
9. BEGIN  
10. SELECT id,name FROM person WHERE id = personId;  
11. END   

#2 - Use same data-type in WHERE clause
It will be impact on performance because MySQL hold extra memory to type conversion.

Example:

1. -- Bad  
2. SELECT name FROM person WHERE id = '1001';  
3. -- Good  
4. SELECT name FROM person WHERE id = 1001;  

#3 - Use EXISTS clause
It will be improve response time where need logic based on existence of record in MySQL.

Example:

1. -- Bad  
2. IF(SELECT COUNT(*) FROM person) > 0;  
3. -- Good  
4. IF EXISTS(SELECT 1 FROM person);   

#4 - Add indexing to column that used to join table
MySQL use index to faster querying data. we can use EXPLAIN SELECT statement that shows how MySQL query optimizer will execute the query.

#5 - Avoid function over indexed column
function over indexed column will be defeat purpose of indexing.

Example:

1. -- Bad  
2. SELECT name FROM person WHERE UPPER(name) LIKE 'J%';  
3. -- Good  
4. SELECT name FROM person WHERE name LIKE 'J%';   

#6 - Prefer ENUM over VARCHAR data-type for multi value column(gender, status, state) for large tables
It will be improve response time.

Example:

1. -- VARCHAR  
2. CREATE TABLE person(  
3. id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,  
4. name VARCHAR(50) NOT NULL,  
5. gender VARCHAR(50)  
6. )ENGINE=MyISAM;  
7. -- ENUM  
8. CREATE TABLE person(  
9. id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,  
10. name VARCHAR(50) NOT NULL,  
11. gender ENUM('Male','Female')  
12. )ENGINE=MyISAM;   

#7 - Avoid SELECT *
As best practice, Always retrieve necessary columns with select statement that improves response time.

#8 - Avoid use of GROUP BY clause without aggregate function
It will be always retrieve first record by grouped column. so, that will be differ if we expect all record based on grouped column.

Example:

1. -- Bad  
2. SELECT id,name FROM person GROUP BY name;  
3. -- Good  
4. SELECT name, count(*) as count FROM person GROUP BY name;

Conclusion:
In this article, we have learned basic keyword/approach that can be helped us to improving performance/troubleshoot in MySQL.