[MySQL] — Improving Performance and Avoiding Problems

1 — When creating a Stored Procedure, do not use the same name in the query parameter in the Where clause and the field name in the query. It will return all records.

DELIMITER $$
CREATE PROCEDURE `getCategoriasPorId`(IN CategoryID INT)  
BEGIN  
   SELECT CategoryID,CategoryName FROM categories WHERE CategoryID=CategoryID;    
END;
$$
DELIMITER ;

2 — Use the EXISTS clause, this improves query response time.

if(select count(*) from categories) > 0
if EXISTS(select count(*) from categories) > 0

3 — Avoid using functions in indexed columns , using the function will override the purpose of the indexed column.

select CategoryName from categories Where UPPER(CategoryName) LIKE`J%`
select CategoryName from categories Where CategoryName LIKE `J%`

4— Prefer to use ENUM to VARCHAR in columns with multiple values (eg gender, status, etc.) . Response time is best with ENUM

-- VARCHAR  
CREATE TABLE Aluno(  
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,  
nome VARCHAR(50) NOT NULL,  
sexo VARCHAR(50)  
)ENGINE=MyISAM;
-- ENUM  
CREATE TABLE Aluno(  
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,  
nome VARCHAR(50) NOT NULL,  
sexo ENUM('Male','Female')  
)ENGINE=MyISAM;

6 — Avoid using SELECT * . If you are selecting only a few columns from a table, avoid using SELECT *.

Although it is easier to write, this will impact the performance of the query. By selecting only the columns you need, you are reducing the size of the results table, reducing network traffic, and further increasing performance.

7 — Avoid using the GROUP BY clause without using aggregate functions.

The query will always retrieve the first record by the grouped column, so that it will be different if we expect all records, which are based on a grouped column.

SELECT CategoryID, Description, CategoryName FROM categories GROUP BY CategoryName;
SELECT CategoryName, count(*) as count FROM categories GROUP BY CategoryName;