Mysql – Local MySQL instance running extremely slow compared to remote version

MySQL

I'm working on a local project and it have a remote version running normally and I did imported all data from external DB to local instance. When I run a query with a specific FUNCTION, it run too slow, but too slow, that never finish (I wait about 10 minutes, and nothing), but on the remote server, it does very fast, less than 1 second.

I checked and copied a lot of mysql configs and saved to my.ini from the remote MySQL instance. Nothing changed. You can see the difference that remained here. Basically, system things, directories, bits difference, etc. The uniques differences that I noted and I can't change is the compiled version (local is 32 bits, remove is 64 bits), MySQL version (local is 5.6.16, remove is 5.6.21).

This problem starts to occur after I create a MySQL FUNCTION that return a "better version" of SOUNDEX native function. It's very simple, and run fine remotely (the same version of function). Basically get a string and return the SOUNDEX for each word, for instance, SOUND_EX("John Doe") returns J530 only. My SOUNDEX_SPLITTED("John Doe", ",") will return J500,D000. With correct usage, it allow I get the name John Server Doe searching "incorrectly" by Jon Doeh.

CREATE DEFINER=`root`@`localhost` FUNCTION `SOUNDEX_SPLITTED`
        (`strValue` TINYTEXT, `strSeparator` TINYTEXT)
    RETURNS tinytext CHARSET latin1
    LANGUAGE SQL
    DETERMINISTIC
    NO SQL
    SQL SECURITY INVOKER
    COMMENT 'Executa um SOUNDEX separando por espaços.'
BEGIN
    DECLARE spaceFirst INT;
    DECLARE spaceLast INT DEFAULT 0;
    DECLARE strLength INT DEFAULT LENGTH(strValue);
    DECLARE soundexPart TINYTEXT;
    DECLARE soundexValue TINYTEXT;

    IF strValue RLIKE "[0-9]" THEN
        RETURN NULL;
    END IF;

    SET strValue = CONCAT(strValue, " ");
    IF strSeparator IS NULL THEN
        SET strSeparator = " ";
    END IF;

    STR_LOOP:
    WHILE spaceLast < strLength DO
        SET spaceFirst = spaceLast + 1;
        SET spaceLast = LOCATE(" ", strValue, spaceFirst);
        SET soundexPart = SUBSTRING(strValue, spaceFirst, spaceLast - spaceFirst);

        IF LENGTH(soundexPart) <= 2 THEN
            ITERATE STR_LOOP;
        END IF;

        SET soundexValue = CONCAT_WS(strSeparator, 
                                     soundexValue, 
                                     SOUNDEX(soundexPart));
    END WHILE;

    RETURN TRIM(soundexValue);
END

If I cancel this function to running completly (like a RETURN SOUNDEX(...) imediatly after declarations), the full query back to work again.

Best Answer

You have at least one bug in your function that allows it to enter an infinite loop on bad input data. Care always has to be taken with loops in stored programs, to be certain this doesn't happen, regardless of the input. I haven't analyzed this thoroughly, so there may be other cases that can break it, but here's the one I spotted.

You're using the input variable strValue internally, and it's a TINYTEXT, which is limited to 2^8-1 (255) characters. But then, you're appending a trailing space.

SET strValue = CONCAT(strValue, " ");

What happens when your input value is >= 255 characters?

mysql> SELECT SOUNDEX_SPLITTED(REPEAT('a',255),',');
-- nothing happens

You have an infinite loop, because you never find the trailing space you're expecting, so this...

WHILE spaceLast < strLength DO

...remains true forever, because this...

SET strValue = CONCAT(strValue, " ");

...did not actually add a space. It couldn't, because there's no room. So the value was truncated back to its original, without a trailing space.

Actually, it's not true that nothing happens. There's a warning generated, if the input is actually larger than 255 characters, and truncated at the input of the function... but you'll never see it, because it won't be visible until after the function runs.

+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'strValue' at row 1 |
+---------+------+-----------------------------------------------+

If the input data is exactly 255 characters, there's a similar warning that should be generated within the function when you try to concatenate that single space, but Oracle has mangled the proper handling of the diagnostics area within running programs in a misguided attempt to make the right errors show up at the right time. Even if it were there, though, you wouldn't see it, since the function is still running in an endless loop.

I'm not suggesting the following is the correct fix, but will easily demonstrate the problem (works with MySQL 5.5 and up):

SET strValue = CONCAT(strValue, " ");
-- verify that we actually did just now add a trailing space 
IF strValue NOT RLIKE '[[:space:]]$' THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'string overflow';
END IF;

Now...

mysql> SELECT SOUNDEX_SPLITTED(REPEAT('a',255),',');
ERROR 1644 (45000): string overflow

A better solution might be to use an internal variable that's of a longer type than the input, and copy the input plus the trailing space into that new variable, or modify the code to find the end in a different way, so that the trailing space isn't required.

Related Question