mySQL: Query performance with User Defined Functions

[Joey JJ]

Hi,

I'm trying to make a multi-language mySQL database. I was considering using user defined functions to determine which column in a table to read - where different columns will store the different translations - however I was concerned about query performance.

For example, if I wanted to return a list of Cities, and the name of country - with the latter returned in multiple languages. At what point would the below approach impact performance? - as I might do an equivalent on a table with 2-5,000 rows.

Country table structure:

SELECT
`CountryID`,
`Name_English`,
`Name_French`,
`Name_Spanish`
FROM `Country`
WHERE `CountryID` = fCountryID;
City Table Structure:

SELECT
`City`.`CityName` 'City'
,getCountry(1, `City`.`CountryID`) 'Country'
FROM `City`;
Example Function Call:

SELECT
`City`.`CityName` 'City'
,getCountry(1, `City`.`CountryID`) 'Country'
FROM `City`;
Full Function:

delimiter $$ CREATE DEFINER=root@localhost FUNCTION getCountry(fLanguageID INT, fCountryID SMALLINT) RETURNS varchar(100) CHARSET utf8 COLLATE utf8_unicode_ci BEGIN

DECLARE returnCountry VARCHAR(100);

IF (fLanguageID = 1) -- English
THEN

SET returnCountry = (

SELECT `Name_English` FROM `Country`
WHERE `CountryID` = fCountryID

);

ELSEIF (fLanguageID = 2) -- French
THEN
SET returnCountry = (

SELECT `Name_French` FROM `Country`
WHERE `CountryID` = fCountryID

);


ELSEIF (fLanguageID = 3) -- Spanish
THEN
SET returnCountry = (

SELECT `Name_Spanish` FROM `Country`
WHERE `CountryID` = fCountryID

);

END IF;

RETURN returnCountry;


SELECT
`CountryID`,
`Name_English`,
`Name_French`,
`Name_Spanish`
FROM `Country`
WHERE `CountryID` = fCountryID;

SELECT
`City`.`CityName` 'City'
,getCountry(1, `City`.`CountryID`) 'Country'
FROM `City`;
END$$