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

You are viewing an old version of this question. View the current version here.

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 56 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

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.

Comments

Comments loading...
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.