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:
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?
When declaring HEX strings such as UUIDs, or when declaring other strings with limited character set needs, such as
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
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.
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
External links referenced by this document: