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
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.