How to increase the speed of a Function

[Victor Gatnicki]

Hello Everyone.

I have a one to many relationship in my Database. One task can have many designations.
I need to display all the designations for each task online so I decided to create a Function that returns
a string of all designations of a certain task.

When I do a SELECT (I use a limit clause) and the result is large (over 1000 results returned) the search takes around 10 seconds.

I would like to reduce the search time. Can anyone suggect anything. Does MySQL use
indexes within Functions?

I am using MySQL 5.0.15. I have attached everything below:

DROP TABLE IF EXISTS cp_designation;
CREATE TABLE cp_designation
(
deskey INT UNSIGNED NOT NULL AUTO_INCREMENT,
designation VARCHAR(50) NOT NULL,
level VARCHAR(30) NOT NULL,
tdesc VARCHAR(255) NULL,
pop INT NULL DEFAULT 1,

/* audit fields user login name and dates*/
/* CONSTRAINTS */
UNIQUE(designation,level),

/* INDEX add indexes for foreign key*/
INDEX (deskey),
INDEX (designation),
INDEX (level),

/* PRIMARY KEY */
PRIMARY KEY(deskey)

) ENGINE=InnoDB;


DROP TABLE IF EXISTS cp_task;
CREATE TABLE cp_task
(
taskey INT UNSIGNED AUTO_INCREMENT NOT NULL,
task VARCHAR(500) NOT NULL,
tdesc VARCHAR(500) NULL,
pop INT NULL DEFAULT 1,
status TINYINT NULL DEFAULT 1,

/* INDEX add indexes for foreign key*/
INDEX (taskey),
INDEX (task),

/* primary key */
PRIMARY KEY(taskey)

) ENGINE=InnoDB;


DROP TABLE IF EXISTS cp_tsklevdes;
CREATE TABLE cp_tsklevDes
(
tldkey INT UNSIGNED AUTO_INCREMENT NOT NULL,
fkey_taskey INT UNSIGNED NOT NULL,
fkey_deskey INT UNSIGNED NOT NULL,

/* CONSTRAINTS */
UNIQUE(fkey_taskey,fkey_deskey),

/* INDEX add indexes for foreign key*/
INDEX(tldkey),
INDEX(fkey_taskey),
INDEX(fkey_deskey),
INDEX(fkey_taskey,fkey_deskey),

/* FOREIGN KEY */
FOREIGN KEY(fkey_taskey) REFERENCES cp_task(taskey)
ON DELETE CASCADE
ON UPDATE CASCADE,

FOREIGN KEY(fkey_deskey) REFERENCES cp_designation(deskey)
ON DELETE CASCADE
ON UPDATE CASCADE,

/* PRIMARY KEY */
PRIMARY KEY(tldkey)

) ENGINE=InnoDB;


delimiter //

DROP FUNCTION IF EXISTS getSearchDes//
CREATE FUNCTION getSearchDes(tskId INT,strDel CHAR(1)) RETURNS VARCHAR(255)
BEGIN

DECLARE done INT DEFAULT 0;
DECLARE tmpDes VARCHAR(100);
DECLARE tmpLev VARCHAR(50);
DECLARE returnStr VARCHAR(255);
DECLARE curDes CURSOR FOR SELECT designation,level FROM cp_tsklevDes INNER JOIN cp_designation ON fkey_deskey = deskey WHERE fkey_taskey =tskId;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
set returnStr = '';
OPEN curDes;

REPEAT
FETCH curDes INTO tmpDes, tmpLev;

IF NOT done THEN
set returnStr = CONCAT(returnStr,tmpDes,' ',tmpLev,strDel);
END IF;
UNTIL done END REPEAT;

RETURN SUBSTRING(returnStr,1,LENGTH(returnStr)-1);
END;
//

delimiter ;

SELECT taskey, task, getSearchDes(taskey,',') from cp_task;

THANK YOU