delimiter //
CREATE OR REPLACE FUNCTION `get_id`(`INPUT_STRING` VARCHAR(50), `DECRYPT_KEY` CHAR(20)) RETURNS int(11)
BEGIN
    DECLARE
        REQUEST_ID INT ;
    SET
        REQUEST_ID =

                IF(
            (
                LOCATE('#$#$#$ LHP_ID_', INPUT_STRING)
            ) > 0 AND
            (
                INPUT_STRING REGEXP DECRYPT_KEY
            )>0,
            (

                SPLIT_STRING(
                    SUBSTRING_INDEX(
                        SUBSTRING_INDEX(INPUT_STRING, '#$#$#$ LHP_ID_', -1),
                        '#$#$#$',
                        1
                    ),
                    '-',
                    2
                )
        ),
        NULL
        ) ;
                SET
                REQUEST_ID=IF(
                ISNULL(REQUEST_ID)OR(REQUEST_ID='')OR(REQUEST_ID=0),
                NULL,
                REQUEST_ID

                );

                RETURN(REQUEST_ID) ;
END
//

delimiter ;


delimiter //
CREATE OR REPLACE FUNCTION `SPLIT_STRING`(`str` VARCHAR(255), `delim` VARCHAR(12), `pos` INT) RETURNS varchar(255) CHARSET utf8mb4
RETURN REPLACE(
        SUBSTRING(
                SUBSTRING_INDEX(str , delim , pos) ,
                CHAR_LENGTH(
                        SUBSTRING_INDEX(str , delim , pos - 1)
                ) + 1
        ) ,
        delim ,
        ''
)
//
delimiter ;

CREATE TABLE `tab1` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME_F` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 CREATE TABLE `tab2` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TAB1_ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;


delimiter //
for i in 1..100 do insert into tab1 values (null,'qwerty'); end for ; //
delimiter ;



delimiter //
for i in 1..1000 do insert into tab2 values (null,round(rand()*10000)); end for ; //
delimiter ;










# Thread_id: 380229  Schema: andre_k  QC_hit: No
# Query_time: 0.028396  Lock_time: 0.001841  Rows_sent: 2  Rows_examined: 10958383778436017378
# Rows_affected: 0  Bytes_sent: 124
# Tmp_tables: 2  Tmp_disk_tables: 0  Tmp_table_sizes: 380968
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
SET timestamp=1677147120;
SELECT

    get_id(CAST(aes_decrypt(tab1.NAME_F,'V41iNM0n4') AS char),'KM_ID_PL') as get_string,

    (CASE WHEN (SELECT ID FROM tab2 where tab2.TAB1_ID = tab1.ID LIMIT 1) IS NULL THEN 0 ELSE 1 END) AS IS_ATT

FROM
    tab1
ORDER BY 2 DESC
LIMIT 2;




using the same function  adding DETERMINISTIC ( same issue):






delimiter //
CREATE OR REPLACE FUNCTION `get_id_determ`(`INPUT_STRING` VARCHAR(50), `DECRYPT_KEY` CHAR(20)) RETURNS int(11) DETERMINISTIC
BEGIN
    DECLARE
        REQUEST_ID INT ;
    SET
        REQUEST_ID =

                IF(
            (
                LOCATE('#$#$#$ LHP_ID_', INPUT_STRING)
            ) > 0 AND
            (
                INPUT_STRING REGEXP DECRYPT_KEY
            )>0,
            (

                SPLIT_STRING(
                    SUBSTRING_INDEX(
                        SUBSTRING_INDEX(INPUT_STRING, '#$#$#$ LHP_ID_', -1),
                        '#$#$#$',
                        1
                    ),
                    '-',
                    2
                )
        ),
        NULL
        ) ;
                SET
                REQUEST_ID=IF(
                ISNULL(REQUEST_ID)OR(REQUEST_ID='')OR(REQUEST_ID=0),
                NULL,
                REQUEST_ID

                );

                RETURN(REQUEST_ID) ;
END
//

delimiter ;





# Thread_id: 380229  Schema: andre_k  QC_hit: No
# Query_time: 0.023764  Lock_time: 0.000318  Rows_sent: 2  Rows_examined: 10958383778436017378
# Rows_affected: 0  Bytes_sent: 124
# Tmp_tables: 2  Tmp_disk_tables: 0  Tmp_table_sizes: 380968
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
SET timestamp=1677147156;
SELECT
get_id_determ(CAST(aes_decrypt(tab1.NAME_F,'V41iNM0n4') AS char),'KM_ID_PL') as get_string,
(CASE WHEN (SELECT ID FROM tab2 where tab2.TAB1_ID = tab1.ID LIMIT 1) IS NULL THEN 0 ELSE 1 END) AS IS_ATT