Stored procedure lasts to long when called-Mysql
Hello,
I'm new here and I hope you can help me, I would be very thankful
I'm using command procesor to develop this SP and using Mysql
I've already have a database created
Ok so this is my code
***************************************
DROP PROCEDURE IF EXISTS search;
DELIMITER ;;
create procedure search
(
IN startTime datetime,
IN endTime datetime,
IN searchString varchar(255)
)
BEGIN
DECLARE t int;
SET t = 0;
SET @outvar = 0;
SET @outvar2 = @outvar;
WHILE startTime<=endTime DO
SET @query = CONCAT('SELECT tableName FROM syslogindex WHERE startDate =\'',startTime,'\' INTO @outvar ;');
PREPARE STMT FROM @query;
EXECUTE STMT;
SET @query3 = CONCAT('CREATE TABLE IF NOT EXISTS `',@outvar,'` (ID int(10) unsigned, CustomerID bigint(20), ReceivedAt datetime, DeviceReportedTime datetime, Facility smallint(6), Priority smallint(6), FromHost varchar(60), Message text, NTSeverity int(11), Importance int(11), EventSource varchar(60), EventUser varchar(60), EventCategory int(11), EventID int(11), EventBinaryData text, MaxAvailable int(11), CurrUsage int(11), MinUsage int(11), MaxUsage int(11), InfoUnitID int(11), SysLogTag varchar(60), EventLogType varchar(60), GenericFileName varchar(60), SystemID int(11), processid varchar(60), checksum int(11))');
PREPARE STMT FROM @query3;
EXECUTE STMT;
IF @outvar2 != @outvar THEN
IF t > 0 THEN
SET @query2 = CONCAT(@query2,' union (select * from `',@outvar, '` WHERE Message LIKE
\'',searchString,'\')')
ELSEIF t < 1 THEN
SET @query2 = CONCAT('(select * from `',@outvar, '` WHERE Message LIKE \'',searchString,'\')');
SET t = 1;
END IF;
END IF;
SET @outvar2 = @outvar;
SET startTime = DATE_ADD(startTime,INTERVAL 1 DAY);
END WHILE;
SET @query2 = CONCAT(@query2,';');
PREPARE STMT FROM @query2;
EXECUTE STMT;
END;;
DELIMITER ;
****************************
The function runs well, but the problem is that it takes too long to compile or take all records
For example, when I call the SP:
call search('2013-02-17','2013-02-17 1:00:00','%port%');
those on dates are the parameters,, and they take too long to get all the records even though it's only a brief preriod of time. This shouldn't be happening.
What command should I use in order to stop this function pass the indicated time?
The function keeps runnig over the time, for example it stays until 5:00:00 and more form the statement above
Hope you can help,
Thank you i adavance
Daniel