Stored Procedure Is Slower Than Query Due to Collation

Question

I'm running two servers with MySql installed. I'm having an issue with a slow stored procedure (approximately 3 secs vs 0.1 seconds), solely on my production server, that is very quick on development and as a stand-alone query. On my production server, I have a stored procedure which simply does:

SELECT * FROM table WHERE uid = p_uid;

p_uid is specified as: p_uid VARCHAR(36) and takes an input like:

'e42018af-e0c5-11e5-a505-f8b156b860fc'

The column is VarChar(36) as well. When I create a second stored procedure that returns the EXPLAIN, it shows plainly that the index is not used. The index is used appropriately if I execute the query outside of the stored procedure.

I've seen a number of mentions of this possibly being a collation issue. The collation of the database, table, and column are latin1 on both servers. I've tried changing the query to specify a character/collation:

SELECT * FROM table WHERE uid = 
CONVERT(p_uid USING latin1) COLLATE latin1_swedish_ci;

and this seemed to do the trick. Even though this worked, I'm not happy with this solution as handling different and erratic behavior from development to production is not a scalable or efficient plan.

How can I prevent this from happening again?

Answer 1

When declaring HEX strings such as UUIDs, or when declaring other strings with limited character set needs, such as country_code, postal_code, zip_code, etc, declare them to be CHARACTER SET ascii. It can be applied just to the column, letting the rest of your columns be utf8mb4 or whatever.

And be consistent between tables so that JOIN works efficient. And, as Rolando says`, be consistent with Stored Routine arguments, etc.

You can either let the collation for ascii be defaulted, or you can be explicit. But be sure to be consistent. Mismatched collation is as bad for performance as mismatched character set.

(If such fields are already latin1, there is no need to switch to ascii.)

UUIDs have a nasty problem when the dataset becomes huge. Because of the seeming randomness of the strings, caching becomes useless when the table is bigger than cache. One small improvement is to convert to BINARY(16) to save space and make it slightly more cacheable. More discussion.

BTW, SHOW CREATE PROCEDURE procname \G provides the info at the end. For example:

mysql> show create function GCDist\G
*************************** 1. row ***************************
            Function: GCDist
            sql_mode:
     Create Function: CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `GCDist`(
    ...
BEGIN
    ...
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci