How to improve the performance of MYSQL stored functions?

Question

MySQL

Good afternoon. There is a stored function, it is maximally optimized for performance, no more squeezing. That's why it's written

DELIMITER $$

CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `SORT_STR`(`string1` VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
    READS SQL DATA
    DETERMINISTIC
BEGIN
DECLARE sort VARCHAR(255);
DECLARE str1 VARCHAR(255);
DECLARE word VARCHAR(255);
CREATE TEMPORARY TABLE IF NOT EXISTS sort_t
(w VARCHAR(255)); 

SET str1 = LOWER(string1); 
    sub_str_loop: LOOP
    SET word = SUBSTRING_INDEX(str1,' ', 1);
    SET str1 = trim(replace(str1, word, ''));
    INSERT INTO sort_t set w = word;
    IF str1 = '' THEN
      LEAVE sub_str_loop;
    END IF;
    END LOOP sub_str_loop; 
  select group_concat(`w` ORDER BY `w` asc SEPARATOR  ' ') as words into sort from sort_t;
delete from  sort_t;
RETURN sort;
END

It does only 1, takes a string as input -> splits it into words -> adds it to a temporary table -> sorts -> returns the sorted string.
There is an InnoDB table - the number of records is 350 thousand.
There is a column - The data in it is something like this [Chieftec A-135 APS-750C power supply]
Update the entire table with a static value, for example null

UPDATE таблица SET колонка = null	
386398 row(s) affected 
Rows matched: 386398  Changed: 386398  Warnings: 0	
17.659 sec

And this is where the magic begins. Incomprehensible increase in execution time.
A small benchmark (the number of rows to be updated - time) Requests go one after another with mixing by record ID so that they would not be in the cache before

1 - 0.031
10 - 0.063
100 - 0.187
1000 - 3.775
10000 - 246.310

It turns out that I will quickly make 10 queries in a stored procedure for 100 lines than 1 for 1000 lines.
What does MySQL need for such queries? There is enough memory, the server is powerful. Where does he slip?
It can be seen that in a stored function, but how to optimize it? why a smaller number of lines is executed in total faster than a larger one?

Answer 1

The whole thread of discussion and problem solving.
sqlinfo.ru/forum/viewtopic.php?pid=40185

Answer 2

Function called on select? If so, I would change the logic:
* Add a "words sorted" column to the source table
* Make an insert and update trigger that fills the "words sorted" column
* select flies, insert became slightly slower.
In general, the application server will not do such things faster? (for example php)