CREATE TABLE `x`(

  `i` INT,

  `i2` INT,

  PRIMARY KEY(`i`),

  KEY (`i2`)

 );

INSERT INTO 

    `x` (

        SELECT 

            seq, 

            FLOOR(0 + RAND() * (1000000 +1))

        FROM 

            seq_1_to_10000000

        );

DELIMITER $$

CREATE PROCEDURE `testSp`()

    MODIFIES SQL DATA

BEGIN

        DECLARE `indexId` INT DEFAULT 71;

        SELECT

            `i2`

        FROM

            (

                SELECT

                    `x`.*,

                    ROW_NUMBER() OVER (

                        PARTITION BY `i2`

                        ORDER BY `i` ASC

                    ) AS `rowNumber`

                FROM

                    `x`

            ) AS `iq`

        WHERE 

            `iq`.`i2` = `indexId`

        LIMIT 1;

    END$$

DELIMITER ;

MariaDB [tflt]> call testSp();

+------+

| i2   |

+------+

|   71 |

+------+

1 row in set (43.560 sec)

MariaDB [(none)]> show explain for 16;

+------+-------------+------------+-------+---------------+------+---------+------+---------+------------------------------+

| id   | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows    | Extra                        |

+------+-------------+------------+-------+---------------+------+---------+------+---------+------------------------------+

|    1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL | NULL    | NULL | 9983371 | Using where                  |

|    2 | DERIVED     | x          | index | NULL          | i2   | 5       | NULL | 9983371 | Using index; Using temporary |

+------+-------------+------------+-------+---------------+------+---------+------+---------+------------------------------+

2 rows in set, 1 warning (0.001 sec)

+------+-------------+------------+------+---------------+------+---------+-------+------+------------------------------+

| id   | select_type | table      | type | possible_keys | key  | key_len | ref   | rows | Extra                        |

+------+-------------+------------+------+---------------+------+---------+-------+------+------------------------------+

|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL  | 16   | Using where                  |

|    2 | DERIVED     | x          | ref  | i2            | i2   | 5       | const | 16   | Using index; Using temporary |

+------+-------------+------------+------+---------------+------+---------+-------+------+------------------------------+

2 rows in set (0.005 sec)

SELECT

            `i2`

        FROM

            (

                SELECT

                    `x`.*,

                    ROW_NUMBER() OVER (

                        PARTITION BY `i2`

                        ORDER BY `i` ASC

                    ) AS `rowNumber`

                FROM

                    `x`

            ) AS `iq`

        WHERE 

            `iq`.`i2` = 71 LIMIT 1;

+------+

| i2   |

+------+

|   71 |

+------+

1 row in set (0.001 sec)