CODEWITHSUNDEEP
mysqlperformancestored-procedures
Bastien
Bastien

Reputation: 13

MySQL Simple Select - Stored Procedure Performance

I'm currently developing a stored procedure on MySQL and I was expecting a slight gain of speed. But when I compared it to SQL request execution through a PHP script, PHP was faster. With a 1000-row table about 10x faster, and with a 6000-row table about 2x faster.

Does the table size improve or not the procedures performances? Did I make a mistake in my code and can I optimize it?

My configuration is MyIsam engine on MySQL 5.0.10. My stored procedure is

CREATE PROCEDURE  get_task (IN var INT)
BEGIN
    DECLARE id_task INT (11);
    DECLARE job INT (11);
    DECLARE state_name VARCHAR(20);
    DECLARE task_name VARCHAR(20);
    DECLARE worker_affected INT(11);
    DECLARE user VARCHAR(10);
    DECLARE progress INT(11);
    DECLARE name VARCHAR(128);
    DECLARE phone VARCHAR(128);
    DECLARE mobile VARCHAR(128);
    DECLARE site VARCHAR(32);
    DECLARE worker_name VARCHAR(20);
    DECLARE date_time_process_started DATETIME;
    DECLARE frame INT(11);

    DECLARE curseur1 CURSOR FOR 

    SELECT tq.`id_task`, tq.`job`, lts.`state_name`, ltt.`task_name`, tq.`worker_affected`, j.`user`, tq.`progress`, u.`name`, u.`phone`, u.`mobile`, u.`site`, w .`worker_name`, tq.`date_time_process_started`, tq.`frame`
    FROM `task_queue` tq
    LEFT JOIN `workers` w ON tq.`worker_affected` = w.`id_worker`
            INNER JOIN `job` j ON tq.`job` = j.`job_id`
            INNER JOIN `user` u ON j.`user` = u.`ipn`
            INNER JOIN `list_task_type` ltt ON tq.`task_type` = ltt.`id_type_task`
            INNER JOIN `list_task_state` lts ON tq.`task_state` = lts.`id_state`
    WHERE tq.`id_task` =  var 
    ORDER BY tq.`id_task`;

    OPEN curseur1;

    FETCH curseur1 INTO id_task, job, state_name, task_name, worker_affected, user, progress, name, phone, mobile, site, worker_name, date_time_process_started, frame;
    SELECT id_task, job, state_name, task_name, worker_affected, user, progress, name, phone, mobile, site, worker_name, date_time_process_started, frame;

    CLOSE curseur1;

END |

Upvotes: 0

Views: 1364

Answers (2)

Bastien
Bastien

Reputation: 13

I follewed your advice and removed the CURSOR and declarations.

CREATE PROCEDURE  get_task (IN var INT)
BEGIN

SELECT tq.`id_task`, tq.`job`, lts.`state_name`, ltt.`task_name`, tq.`worker_affected`, j.`user`, tq.`progress`, u.`name`, u.`phone`, u.`mobile`, u.`site`, w .`worker_name`, tq.`date_time_process_started`, tq.`frame`
FROM `task_queue` tq
LEFT JOIN `workers` w ON tq.`worker_affected` = w.`id_worker`
        INNER JOIN `job` j ON tq.`job` = j.`job_id`
        INNER JOIN `user` u ON j.`user` = u.`ipn`
        INNER JOIN `list_task_type` ltt ON tq.`task_type` = ltt.`id_type_task`
        INNER JOIN `list_task_state` lts ON tq.`task_state` = lts.`id_state`
WHERE tq.`id_task` =  var 
ORDER BY tq.`id_task`;


END |

Indeed performances skyrocketed and now my stored procedure is only twice slower than the PHP script (0.0006 vs 0.0012 seconds against 0.0006s vs 0.009s previously).

And seeing the stored procedure's code I understand why you said it was useless, but I will keep it in order to force the database users through functions and procedures in their websites. I feel more secure like that.

Thanks a lot.

Upvotes: 1

Ross Smith II
Ross Smith II

Reputation: 12179

Your stored procedure, as written, is completely unnecessary.

Not only do you not need a CURSOR to return a result set, you don't even need the procedure, just to run the single SELECT statement.

Just include the SELECT in your PHP code.

Upvotes: 0

Related Questions