Mysql repeated CONCAT operations optimization

Here is a story of how we optimized a MySQL stored procedure with repeated CONTACT operations.

Let's consider we have the following temp_test table:

For simplicity, it will consist of only two columns autogenerated id and some text column name. And we have a stored procedure that is inserting new rows in a loop to this table temp_test. As a result of our procedure, we want a list containing all new row ids that were generated. But as MySQL stored procedures don’t support lists as a return type we need to use a string with concatenated ids as a return value.

In this script, we hardcoded the number of iterations to 50k to show the performance bottleneck. After some procedure runs we calculated its average calculation time equal to 150 sec on my local env. With the increase in the number of processed rows, things are only getting worse.

It turned out that this solution doesn’t suit well for big amounts of data as with the growth of the string CONTACT operations are getting pretty slow. And there is a better option to solve this problem using temp tables. Temporary is visible only within the current session, and is dropped automatically when the session is closed.

Here is how a new version of test_proc looks like:

I create a temp table ids_tmp to store autogenerated ids. After all the rows were created I read all the identifiers from the temp table and concatenate them with a single GROUP_CONTACT operation.

This improvement gives me an average execution time of 110 seconds. Which is about 25% better at only 50k of records.