Post
Ask Question

MySQL - Best Practices

In this blog, we are going to learn a few good habits that we can consider while working with MySQL to improve performance & troubleshoot, as shown below.
 
Do not use the same stored procedure & function parameter name as the WHERE clause field name

It will respond with all the records of the query because MySQL interprets the field value as a parameter value, which is similar to 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   
Use same data-type in WHERE clause

It will impact the performance because MySQL holds extra memory for the type conversion.
 
Example
  1. -- Bad  
  2. SELECT name FROM person WHERE id = '1001';  
  3. -- Good  
  4. SELECT name FROM person WHERE id = 1001;   
Use EXISTS clause 

It will improve the response time, where the need is logic based on the existence of the record in MySQL.
 
Example
  1. -- Bad  
  2. IF(SELECT COUNT(*) FROM person) > 0;  
  3. -- Good  
  4. IF EXISTS(SELECT 1 FROM person);   
Add indexing to column that used to join table

MySQL uses index to query data faster. We can use EXPLAIN SELECT statement, which shows how MySQL query optimizer will execute the query.
 
Avoid function over the indexed column

The function over indexed column will defeat the 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%';   
Prefer ENUM over VARCHAR data-type for multi value column(gender, status, state) for large tables

It will improve the 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;   
Avoid SELECT *

As a best practice, always retrieve the necessary columns with the select statement that improves the response time.
 
Avoid use of GROUP BY clause without aggregate function

It will always retrieve the first record by the grouped column, so that will differ, if we expect all the records, which are 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 blog, we learned the basic keyword/approach that can help us in improving the performance/ troubleshooting in MySQL.