MySQL Forums
Forum List  »  Stored Procedures

How to increase the speed of a Function
Posted by: Victor Gatnicki
Date: November 07, 2005 03:31PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
How to increase the speed of a Function
3476
November 07, 2005 03:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.