For a Project I had helped work on, they recently needed to move servers and in the process upgraded from MySQL 5.0 to MySQL 5.1.41. They ran into some quirks with 5.1 and I thought I would document our work-a-rounds for them.
We were having serious performance problems with a Stored Procedure that would be called by a Windows Client on thousands of machines. Before on 5.0 it would take under 0.02 seconds, and now it was taking 25-35 seconds. In this stored procedure we would call a query something like this:
SELECT u.id INTO spUserId FROM users u WHERE u.username = spUsername;
Where spUsername was a Stored Procedure variable of CHAR(50). So while the stored procedure was running I would execute a SHOW FULL PROCESSLIST; to see what the matter was. I would see the query, but MySQL 5.1 had substituted spUsername with a MySQL function, so it looked like this if the value of spUsername was “sheldon”:
SELECT u.id INTO spUserId FROM users u WHERE u.username = NAME_CONST(‘userName’,_utf8’sheldon’ COLLATE ‘utf8_unicode_ci’);
So when I did an explain (and removed the INTO spUserId since it wasn’t needed to understand the lookup and would throw and error), I saw it was performing a full table scan. The table had near one million rows, so that was a very bad thing. It was completely ignoring the Index on u.username. At first I thought it was because of the NAME_CONST function, that it was doing something weird. To be honest, I was confused as to exactly why it was substituting it in the first place, since the documentation for NAME_CONST didn’t have anything to do with Stored Procedures.
Then I removed “_utf8” and “COLLATE ‘utf8_unicode_ci'” from the picture. Now I was getting somewhere, since it returned 1 row using the correct index. Now, the table users stored its data in the character set latin1_swedish_ci. So, ultimately, from what I can tell, it appears MySQL was reading the entire table, converting the table’s username field to utf8, instead of converting spUserName to latin1. The reason it thought to use utf8 was because the Windows Client when connecting set it’s session to use utf8 (I think, since I couldn’t quickly look at the source and verify this, but it was the assumption I made).
So I decided to manually cast the variable from UTF8 to Latin1 by using another Stored Procedure variable, executing this command:
SET @tmpUserName = CAST(userName AS CHAR CHARACTER SET latin1);
Then I modified my query to use the new variable:
SELECT u.id INTO spUserId FROM users u WHERE u.username = @tmpUserName;
Bingo, I was back down to 0.0022 seconds for the Stored Procedure to execute, and the new Database server is screaming fast.
3 thoughts on “MySQL Stored Procedure, NAME_CONST, and Character Sets”
Thanks a lot for this. Saved me a lot of time.
I overcame this problem using the perpare statement. When you prepared the sql with inserting the necessary placeholders using say concat(‘select * from tab1 where col1=’,quote(col1_value),’…’) we get the same effect.
We are seeing some table scans and we are seeing these in the logs and we were like what the hell. Thank you for this post, this sheds some light.