MySQL Cursor using a lot of memory

Question

I am fairly new to MySQL and have converted a MSSQL query that worked just fine to MySQL, but am finding the MySQL version eventually runs out of memory on my Mac and stops. Obviously, there was a flaw in my conversion effort. :(

I need to look through a table (CurrentPages) that has 88,000 rows, trying to determine if its ContentBody column (of type LongText) contains any of a series of values in a second table (Plugins) which has 300 rows. The Mask column in the Plugin table contains something like '%{macro%'.

I use a stored procedure to iterate through these 2 tables, storing any "hits" found in a third table named Usage.

Create TABLE IF NOT EXISTS CurrentPages (
   ContentID      BIGINT(20)   NOT NULL PRIMARY KEY,
   SpaceKey       VARCHAR(255) NULL,
   PageTitle      VARCHAR(255) NULL,
   ViewURL        VARCHAR(255) NULL,
   OriginalAuthor VARCHAR(255) NULL,
   LastChangedBy  VARCHAR(255) NULL,
   LastChangedDt  VARCHAR(10)  NULL,
   ContentBody    LONGTEXT NULL
);

Create TABLE IF NOT EXISTS Plugins (
    MacroType       INT(10)         NOT NULL PRIMARY KEY,
    PluginName      VARCHAR(255)    NOT NULL,
    MacroName       VARCHAR(255)    NOT NULL,
    Mask            VARCHAR(255)    NOT NULL,
    Disposition     VARCHAR(255)    NOT NULL
);

Create TABLE IF NOT EXISTS Usage (
    ID              INT(10)         NOT NULL AUTO_INCREMENT PRIMARY KEY,
    MacroType       INT(10)         NOT NULL,
    SpaceKey        VARCHAR(255)    NOT NULL,
    PageTitle       VARCHAR(255)    NOT NULL,
    ViewURL         VARCHAR(255)    NOT NULL,
    PluginName      VARCHAR(255)    NOT NULL,
    MacroName       VARCHAR(255)    NOT NULL,
    Disposition     VARCHAR(255)    NOT NULL
);

I realize this truly is a lot of work, but I would have thought it could be done with a cursor. Below is my stored procedure. It writes roughly 96,000 rows to the Usage table before my MySQL client (Navicat) stops responding due to insufficient memory.

Can anyone see what I am doing wrong? Any help would be greatly appreciated! ;)

DELIMITER //
CREATE PROCEDURE pluginanalysis()
BEGIN
  -- Declare variables used with cursor to fetch usage info
  DECLARE v_macrotype       INT(10)         DEFAULT 0;
  DECLARE v_pluginname      VARCHAR(255)    DEFAULT "";
  DECLARE v_macroname       VARCHAR(255)    DEFAULT "";
  DECLARE v_mask            VARCHAR(255)    DEFAULT "";
  DECLARE v_disposition     VARCHAR(255)    DEFAULT "";
  DECLARE v_num_rows        INT(10)         DEFAULT 0;
  DECLARE v_loop_counter    INT(10)         DEFAULT 0;
    DECLARE v_no_more_rows    BOOLEAN;

  -- Declare cursor "c" to populate Usage table with info about where macros are used
  DECLARE c CURSOR FOR 
    SELECT MacroType, PluginName, MacroName, Mask, Disposition FROM appfire_Plugins;
  -- Declare NOT FOUND handler  
  DECLARE CONTINUE HANDLER FOR NOT FOUND 
    SET v_no_more_rows = TRUE;  

  -- Open cursor "c"  
  OPEN c;
  SELECT FOUND_ROWS() INTO v_num_rows;

  SELECT 'Number of rows =', v_num_rows;

  get_usage_loop: LOOP
    FETCH c 
           INTO v_macrotype, 
                v_pluginname, 
                v_macroname, 
                v_mask, 
                v_disposition;

        -- Break out of the loop if there were no records or we've processed them all
        IF v_no_more_rows THEN
      CLOSE c;
      LEAVE get_usage_loop;
    END IF;

        INSERT INTO Usage (SpaceKey, PageTitle, ViewURL, MacroType, PluginName, MacroName, Disposition)
            SELECT SpaceKey,
                PageTitle,
                ViewURL,
                v_macrotype,
                v_pluginname,
                v_macroname,
                v_disposition
           FROM CurrentPages
           WHERE ContentBody LIKE v_mask;

          SET v_loop_counter = v_loop_counter + 1;

  END LOOP get_usage_loop;

    -- Show both counters; they should match if all rows were processed 
  SELECT v_num_rows, v_loop_counter;
END //
-- Restore standard delimiter (semicolon)
DELIMITER ;




-- Now call the stored procedure
CALL pluginanalysis();

Answer 1

Set operations are usually faster than using cursors, in both MySQL and SQL Server.

I think the following query can replace all the cursor code:

    INSERT INTO Usage(SpaceKey, PageTitle, ViewURL, MacroType, PluginName, MacroName, Disposition)
        SELECT cp.SpaceKey, cp.PageTitle, cp.ViewURL,
               ap.macrotype, ap.pluginname, ap.macroname, ap.disposition
       FROM Appfire_Plugins ap JOIN
            CurrentPages cp
            ON cp.ContentBody LIKE ap.mask;