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
-
- CREATE PROCEDURE `getPersonById`(IN id INT(10))
- BEGIN
-
- SELECT id,name FROM person WHERE id = id;
- END
-
- CREATE PROCEDURE getPersonById(IN personId INT(10))
- BEGIN
- SELECT id,name FROM person WHERE id = personId;
- END
Use same data-type in WHERE clause
It will impact the performance because MySQL holds extra memory for the type conversion.
Example
-
- SELECT name FROM person WHERE id = '1001';
-
- 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
-
- IF(SELECT COUNT(*) FROM person) > 0;
-
- 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
-
- SELECT name FROM person WHERE UPPER(name) LIKE 'J%';
-
- 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
-
- CREATE TABLE person(
- id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- name VARCHAR(50) NOT NULL,
- gender VARCHAR(50)
- )ENGINE=MyISAM;
-
- CREATE TABLE person(
- id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- name VARCHAR(50) NOT NULL,
- gender ENUM('Male','Female')
- )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
-
- SELECT id,name FROM person GROUP BY name;
-
- 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.