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

-- Bad
CREATE PROCEDURE `getPersonById`(IN id INT(10))
BEGIN
-- return all record instead
SELECT id,name FROM person WHERE id = id;
END
-- Good
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

-- Bad
SELECT name FROM person WHERE id = '1001';
-- Good
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

-- Bad
IF(SELECT COUNT(*) FROM person) > 0;
-- Good
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

-- Bad
SELECT name FROM person WHERE UPPER(name) LIKE 'J%';
-- Good
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

-- VARCHAR
CREATE TABLE person(
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
gender VARCHAR(50)
)ENGINE=MyISAM;
-- ENUM
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

-- Bad
SELECT id,name FROM person GROUP BY name;
-- Good
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.