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?