Slow Stored Procedure Execution Due to Inconsistent Collations

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 collation of the stored procedure is inconsistent with that of the related table and database. 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 view the definitions of the stored procedure and related table and check whether the collations are the same:

SHOW CREATE PROCEDURE xxx; 
SHOW CREATE TABLE xxx 

Example:

mysql> 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, but the collation of the database is utf8_general_ci by default. The collations are inconsistent, which may cause performance issues.

Solution

Change the collation of the stored procedure to be the same as that of the related table and database.