The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-17. For the latest, interactive version please visit https://mariadb.com/kb/.

CSV style LONGTEXT string into table splitted by comma TO multiple rows (PERFORMANCE)

I have a LONGTEXT string as "A20210917_0701000,B20210917_0701033,A20210917_0703067, ......" (CSV style)

The data is in one row (One column). And it contains elements upto 350,000

In above case, like this ...

Element #1 = A20210917_0701000
Element #2 = B20210917_0701033
...
Element #350,000 = A20210917_2232072

BUT... problem is...

SET @ABC = (SELECT MyLongColumn FROM MyTable ORDER BY MyCond LIMIT 1);  
-- THIS IS NO PROBLEM, FAST LOADED.

But, this LONGTEXT data makes all SQL works TOO SLOW.

SELECT LENGTH(@ABC) - LENGTH(REPLACE(@ABC, ',', ''));  
-- THIS SIMPLE SQL COMMAND TOOKS 90 SECONDS.


SELECT REPLACE(@ABC, ',', '::');    
-- THIS SIMPLE SQL COMMAND TOOKS ABOUT 5~6 MINUTE


SELECT REPLACE(@ABC, ',', ':');    
-- THIS EXECUTED FAST !! (Under 100ms)

I WANT ALL OF THIS ELEMENTS ARE SAVED AS TABLE. LIKE THIS.

VALUE
A20210917_0701000    (Row #1)
B20210917_0701033    (Row #2)
...
A20210917_2232072    (Row #350000)

I'm using MariaDB 10.3.11.

Is there any solution? (performance)

Answer Answered by Daniel Black in this comment.

I'd save this to a file, SELECT .. INTO OUTFILE, or otherwise.

And reimport it with:

LOAD DATA INFILE specifying the comma as the "LINES TERMINATE BY" character (an set "FIELDS SEPARATE BY" to something else.

There's really not performant way to do this in SQL, especially a 10.3 version.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.