Okeh, there are issues before we get to benchmarking.
The MySQL Load Data Infile command is finicky, to put it mildly, eg MySQL accepts NULL as inputs to auto_increment columns in INSERT statements, so you'd expect it to accept NULL as a LOAD DATA INFILE input for an auto_increment column, but it does not. Yes that's unbelievable, but you'll not find support for NULL auto_increment inputs at
https://dev.mysql.com/doc/refman/8.0/en/load-data.html---the recommendation is to leave the auto_increment column off the list of columns provided to the Load Data command, and naturally also leave them out of the input file.
MySQL will accept 0, and it will substitute an auto_incrementing value for such 0s. Possibly WB internally automagically turns such NULLs into 0s, but to get at your performance issue directly I'm bypassing WB, using the mysql client program. When your input file is touched up and a standard Load Data is created for it ...
load data infile "c:/in/memberdata.csv"
into table united_members
fields terminated by ","
lines terminated by "\r\n"
(COMPANY_CODE,LOB,MemberCIN,MemberMedicareID,MemberSubscriberID,MemberFName,MemberMI,MemberLName,MemberGender,MemberDOB,MemberAddr1,MemberAddr2,MemberCity,MemberState,MemberZip,ProviderIDInternal,ProviderNPI,MedicarePIN,ProviderFullName,MemberPhoneHome,MemberRisk,ProviderTIN,EligStart,EligEnd,DIAMOND_ID,EthnicCodeDesc,Practice,Payer,YearMonth,Orig_Filename);
... your input file generates these errors ...
Warning (Code 1265): Data truncated for column 'COMPANY_CODE' at row 1
Warning (Code 1265): Data truncated for column 'MemberSubscriberID' at row 1
Warning (Code 1265): Data truncated for column 'MemberMI' at row 1
Warning (Code 1265): Data truncated for column 'MemberGender' at row 1
Warning (Code 1265): Data truncated for column 'MemberDOB' at row 1
Warning (Code 1265): Data truncated for column 'MemberState' at row 1
Warning (Code 1265): Data truncated for column 'ProviderNPI' at row 1
Warning (Code 1265): Data truncated for column 'ProviderTIN' at row 1
Warning (Code 1265): Data truncated for column 'EligStart' at row 1
Warning (Code 1265): Data truncated for column 'EligEnd' at row 1
Warning (Code 1262): Row 1 was truncated; it contained more data than there were input columns
Warning (Code 1265): Data truncated for column 'EligStart' at row 2
Warning (Code 1265): Data truncated for column 'Payer' at row 2
Warning (Code 1262): Row 2 was truncated; it contained more data than there were input columns
Warning (Code 1265): Data truncated for column 'EligStart' at row 3
Warning (Code 1265): Data truncated for column 'Payer' at row 3
Warning (Code 1262): Row 3 was truncated; it contained more data than there were input columns
Warning (Code 1265): Data truncated for column 'EligStart' at row 4
Warning (Code 1262): Row 4 was truncated; it contained more data than there were input columns
Warning (Code 1265): Data truncated for column 'EligStart' at row 5
Warning (Code 1265): Data truncated for column 'Payer' at row 5
Warning (Code 1262): Row 5 was truncated; it contained more data than there were input columns
Those errors evidence mismatches between the .csv input file and the table DDL. If WB is encountering such errors in the input file you gave it, your MySQL error log should be full of such errors, one for each row! Writing those out would indeed be slow :-).
So I'm wondering how that .csv and that table were generated.
Edited 1 time(s). Last edit at 07/01/2021 02:02PM by Peter Brawley.