Modifying Oracle procedure for MySQL

Question

I am really struggling to modify an Oracle procedure to MySQL, as using cursors in MySQL increases the run time from 2 minutes to 6 hours!!

Here is my Oracle Master Procedure which calls other procedures but i believe it might need a complete restructuring to work for MySQL:

create or replace PROCEDURE lpUsers_master AS
    v_SEASON_NUMBER_DERIVED NUMBER;
    v_LEAGUE_COUNTRY VARCHAR2(100);
    v_LEAGUE_LEVEL NUMBER;
    v_ALPHA2CODE_DERIVED VARCHAR2(2);
    v_count NUMBER;
    v_count_plus NUMBER;
BEGIN
    -- Step 1: Derive the Season Number
    ADMIN.sp_deriveSeasonNumber(v_SEASON_NUMBER_DERIVED);

    -- Step 2: Scan the LEAGUE_POOL table
    FOR r IN (SELECT lp.LEAGUE_POOL_ID, lp.LEAGUE_COUNTRY, lp.LEAGUE_LEVEL FROM LEAGUE_POOL lp) LOOP
        v_LEAGUE_COUNTRY := r.LEAGUE_COUNTRY;
        v_LEAGUE_LEVEL := r.LEAGUE_LEVEL;

        -- Step 3: Derive ALPHA2CODE_DERIVED and check for existing records
        CASE r.LEAGUE_COUNTRY
            WHEN 'Rest of Africa' THEN v_ALPHA2CODE_DERIVED := 'BJ';
            ELSE 
                -- Fetch from the COUNTRIES table
                SELECT ct.ALPHA2CODE INTO v_ALPHA2CODE_DERIVED
                FROM COUNTRIES ct WHERE ct.LEAGUE_COUNTRY = r.LEAGUE_COUNTRY;
        END CASE;

        -- Step 4: Check initial count to see if the league exists in current season
        SELECT COUNT(*) INTO v_count FROM LEAGUE_POOL_USERS lpu
        WHERE lpu.LEAGUE_POOL_ID = r.LEAGUE_POOL_ID AND lpu.SEASON_NUMBER = v_SEASON_NUMBER_DERIVED;

        IF v_count = 0 THEN
                SELECT COUNT(*) INTO v_count_plus 
                FROM LEAGUE_POOL_USERS 
                WHERE LEAGUE_POOL_ID = r.LEAGUE_POOL_ID AND SEASON_NUMBER = v_SEASON_NUMBER_DERIVED + 1;

                IF v_count_plus = 0 THEN
                -- Step 6: If the LEAGUE_POOL_ID doesn't exist in LEAGUE_POOL_USERS (and has not been added already to the next season), 
                -- THEN add NEW users to the database and create new leagues for new season
              sp_insertLeaguePoolUsers(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY, v_LEAGUE_LEVEL, r.LEAGUE_POOL_ID, v_ALPHA2CODE_DERIVED);
                END IF;

        ELSIF v_count > 0 THEN
               -- if it exists, check if the players have already been added to the next season to prevent duplication
            SELECT COUNT(*) INTO v_count_plus 
            FROM LEAGUE_POOL_USERS 
            WHERE LEAGUE_POOL_ID = r.LEAGUE_POOL_ID AND SEASON_NUMBER = v_SEASON_NUMBER_DERIVED + 1;   
            IF v_count_plus = 0 THEN
          -- Step 5: add all league levels to the next season
                CASE r.LEAGUE_LEVEL
                    WHEN 1 THEN
                        ADMIN.sp_handleleaguelevel1(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY);
                         WHEN 2 THEN
                        ADMIN.sp_handleLeagueLevel2(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY);
                        WHEN 3 THEN
                        ADMIN.sp_handleLeagueLevel3(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY);
                         WHEN 4 THEN
                        ADMIN.sp_handleLeagueLevel4(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY);
                END CASE; 
            END IF;
        END IF;
    END LOOP;

END lpUsers_master;

User cursors in MySQL but the performance is terrible

Answer 1

• Some little things to do: Use EXISTS instead of COUNT. The may be much faster, and is probably never slower.

  IF v_count = 0 THEN
            SELECT COUNT(*) INTO v_count_plus 
            FROM LEAGUE_POOL_USERS 
            WHERE LEAGUE_POOL_ID = r.LEAGUE_POOL_ID
                AND SEASON_NUMBER = v_SEASON_NUMBER_DERIVED + 1;
            IF v_count_plus = 0 THEN  ...
  

-->

  IF (v_count = 0) AND NOT EXISTS (
         SELECT 1 FROM LEAGUE_POOL_USERS 
            WHERE LEAGUE_POOL_ID = r.LEAGUE_POOL_ID
                AND SEASON_NUMBER = v_SEASON_NUMBER_DERIVED + 1 )
           THEN ...

• Check for composite indexes, which could be faster than single-column indexes.

• Can the 4 sp_handleleaguelevel1 procs be combined?

• Turn the processing inside-out to avoid cursors.