I have a MySQL procedure with a cursor. The example cursor query is given below
DECLARE cs CURSOR FOR (SELECT a.mag_id FROM A a WHERE a.creation_date BETWEEN (v_fromdate) AND (v_todate) AND a.type_id IN (SELECT type_id FROM key2 WHERE sessionId=v_sessionId) AND a.mag_id IN (SELECT magid FROM key1 WHERE sessionId=v_sessionId order by magid) ) UNION (SELECT b.combo_mag_id FROM B b WHERE b.creation_date BETWEEN (v_fromdate) AND (v_todate) AND b.type_id IN (SELECT type_id FROM key2 WHERE sessionId=v_sessionId) AND b.combo_mag_id IN (SELECT magid FROM key1 WHERE sessionId=v_sessionId order by magid) ); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SET v_cur_time = now(); SET v_cur_query = 'cursor'; OPEN cs; SELECT timestampdiff(SECOND,v_cur_time,now()) into v_diff; SELECT CONCAT('Current Query: ', v_cur_query, ' Time taken: ', v_diff);
Both Table A and Table B have millions of records. I've created partitions in both tables with partition by area at creation date. For 3 months of a date range, it takes almost 4 minutes to execute the cursor. But when I tried to execute the same query in The MySQL Workbench Editor took only 22 seconds to set the parameters for the same date range. Can anyone tell me why it runs faster in an SQL editor, and since I need to use it in the stored procedure, is there a way to tweak it?