Subject Re: complex indexes vs index key fields
From Akshat Kapoor <akshat.kapoor@kapoorsons.in>
Date Tue, 28 Jul 2020 15:06:34 +0530
Newsgroups dbase.getting-started

>> I then tried to append the data from my DBF file to the server but
>> that is taking forever, about 350 records per minute... can I
>> interrupt the append process without corrupting teh database? can you
>> suggest a more efficient way to load data to the database server?
>
> I don't use MySQL but I could insert the 7577 records in the test data
> you sent me into Firebird in 45 seconds.  That's not particularly fast
> but it's a bit quicker than 3.6 hours at 35 records per minute. :-)
>
> MySql is known to be geared more to reading speed rather than writing
> speed but 35 records a minute is ridiculous.  There has to be a way of
> speeding things up.  Akshat can be of more help here than I can as I
> don't use MySql but if you show us how you load the data perhaps we can
> help.

Good Afternoon Mervyn,
It is 350 records per minute not 35 records

And that even is slow by MYSQL standards.

The difference could be due to execution 1 query for every row, and this
indeed slows down speed.

Inserting more than 1 row in the same query will probably be taking the
same time. If not the same time then an increase of just single digit %.

I did change a code from row by row processing to bulk processing and
time improvement was considerable. I did not time the old and new ones
but it was something like 10+ minutes to approx 1 minute.

Regards
Akshat