[julia-users] MYSQL/MARIADB Big INSERT

[Andrey Stepnov]

Hi Community!

I just try to INSERT around 580K rows to MaraDB-table from array B:

julia> B
586973x4 Array{Float64,2}:
 54.1958  142.929  0.15  333.636
…


using MySQL
con = mysql_connect("127.0.0.1", "DB", "PASS", "srss_api_production")

# convert to tuple for big insert
pgavals = Array{Tuple{Float64,Float64,Float64,Float64}}(0)                      
                
for i in 1:length(B[:,1])
        pgavals = push!(pgavals, (B[i,1], B[i,2], B[i,3], B[i,4]))
end

julia> pgavals
586973-element Array{Tuple{Float64,Float64,Float64,Float64},1}:
 (54.195835,142.929169,0.15,333.63588213260454)
...


# prepare
mysql_stmt_prepare(
                con, 
                """
                        INSERT INTO srss_api_production.pga_data 
                        (report_id, version_id, lat, lon, pga_value, ep_dis) 
                        values 
                        (10, 2, ?, ?, ?, ?);
                """
                )
# insert
for i in pgavals
        mysql_execute(
                        con, 
                        [MYSQL_TYPE_FLOAT, 
                         MYSQL_TYPE_FLOAT, 
                         MYSQL_TYPE_FLOAT, 
                         MYSQL_TYPE_FLOAT], 
                         i
                         )  
end


AND it extremely slow! I think because it generates 1 INSERT request for each 
row…

Please help me find out simple (or probably not) way to make INSERT faster.

Andrey