Welcome Guest, Not a member yet? Register   Sign In
How to insert thousands of entrys in MySQL

I'm developing a system in CodeIgniter 4 in which each establishment that is registered will record about 5 thousand products (up to 30 thousand in some cases) daily. The initial idea was that these establishments would upload this information through CSV files, but the solutions I found on the internet run a loop with each CSV line generating an insert in MySQL, this already takes a long time on my local computer, imagine in a server with several establishments uploading information and more users performing queries.

I've just come to the conclusion that it won't be possible to implement this way, so I'll have to think of another solution. I thought about uploading only csv or json files and trying to do the necessary queries on those files, but it doesn't seem efficient (or even viable). So I'd like to know if there is a better alternative to upload these large volumes of information to the server, if there is a solution using MySQL it is even better, but if there are other solutions, they will also be very useful.

use one insert with multiple groups of values - much much faster than using an insert for each line of values. google for insert multiple rows
insert into ... (fieldname, fieldname,...) values (line0_fieldval0, line0_fieldval1, line0_fieldval2), (line1_fieldval0, line1_fieldval1, line1_fieldval2) ad nauseum

using a single insert with 30k lines of values would cause a response timeout but on your submission form but you could just use a javascript loop on the form to repeatedly call a php function until you've processed all 30k
Thumbs Up 

Thank you Badger!
It was exactly what I needed!
Following your instructions I solved my problem through insertBatch from Query Builder Class. It can see my code below.

PHP Code:
    $csvTotal array_chunk($csvArr500);
foreach($csvTotal as $csvPartial){

Thanks again!

Read about insertBatch is the userguide

Theme © iAndrew 2016 - Forum software by © MyBB