Slow Stored Procedure Execution Due to Inconsistent Character Sets

Scenario

It took more than a minute to process just a small amount of data using a stored procedure in an RDS for MySQL instance. Executing the SQL statement in the stored procedure was much faster.

Possible Causes

The stored procedure and the related table or database use different character sets. As a result, a large number of characters need to be converted in the query result and the execution is slow.

Troubleshooting:

Run the following commands to check the definitions of the stored procedure and related table and check whether their character sets are the same:

SHOW CREATE PROCEDURE xxx; 
SHOW CREATE TABLE xxx; 

Example:

SHOW CREATE PROCEDURE testProc \G 
*************************** 1. row ***************************            
Procedure: showstuscore             
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION     
Create Procedure: xxx 
character_set_client: utf8mb4 
collation_connection: utf8mb4_general_ci   
Database Collation: utf8_general_ci 
1 row in set (0.01 sec) 

The collation of the stored procedure is utf8mb4_general_ci, while that of the database is utf8_general_ci by default. The collations are inconsistent, which may lead to performance issues.

Solution

Change the character set to ensure that the stored procedure and the related table or database use the same character set.