Re: server on development machine
I have two tables right now.
The one below has more columns and more rows. I'm not able to complete an import of a file to it, even overnight. A typical file has 200,000 rows.
CREATE TABLE `united_claims` (
`United_Clm_Key` bigint unsigned NOT NULL AUTO_INCREMENT,
`COMPANY_CODE` varchar(5) DEFAULT NULL,
`LOB` varchar(30) DEFAULT NULL,
`Svc_Type` varchar(20) DEFAULT NULL,
`SEQ_CLAIM_ID` varchar(50) DEFAULT NULL,
`Claim_Number` varchar(36) DEFAULT NULL,
`Claimline_Number` smallint DEFAULT NULL,
`Claimline_AdjFlag` char(1) DEFAULT NULL,
`Claim_SvcBeginDate` date DEFAULT NULL,
`Claim_SvcEndDate` date DEFAULT NULL,
`Claim_PaidDate` date DEFAULT NULL,
`Claim_PrintDate` date DEFAULT NULL,
`CHECK_NUMBER` varchar(16) DEFAULT NULL,
`Claimline_ProcCode` varchar(8) DEFAULT NULL,
`Claimline_Mod1` varchar(3) DEFAULT NULL,
`Claimline_Mod2` varchar(3) DEFAULT NULL,
`Claimline_Mod3` varchar(3) DEFAULT NULL,
`Claimline_Mod4` varchar(3) DEFAULT NULL,
`Claimline_RevCode` varchar(8) DEFAULT NULL,
`Claimline_RecDate` date DEFAULT NULL,
`Claimline_AdjudDate` date DEFAULT NULL,
`Claimline_SvcBeginDate` date DEFAULT NULL,
`Claimline_SvcEndDate` date DEFAULT NULL,
`SvcProvID` varchar(17) DEFAULT NULL,
`SvcProvFullName` varchar(75) DEFAULT NULL,
`SvcProvNPI` varchar(10) DEFAULT NULL,
`SvcProvSpecCode` varchar(15) DEFAULT NULL,
`SvcProvType` varchar(15) DEFAULT NULL,
`SvcProvParStat` char(1) DEFAULT NULL,
`AttProvID` varchar(17) DEFAULT NULL,
`AttProvFullName` varchar(75) DEFAULT NULL,
`AttProvNPI` varchar(10) DEFAULT NULL,
`RefProvID` varchar(17) DEFAULT NULL,
`RefProvFullName` varchar(75) DEFAULT NULL,
`VendorIDInternal` varchar(20) DEFAULT NULL,
`VendorFullName` varchar(55) DEFAULT NULL,
`VendorTIN` varchar(9) DEFAULT NULL,
`MemberSubscriberID` varchar(14) DEFAULT NULL,
`PAT_CONTROL_NO` varchar(38) DEFAULT NULL,
`MemberDOB` date DEFAULT NULL,
`MemberLName` varchar(35) DEFAULT NULL,
`MemberMI` char(1) DEFAULT NULL,
`MemberFName` varchar(30) DEFAULT NULL,
`MemberCIN` varchar(20) DEFAULT NULL,
`MemberMedicareID` varchar(12) DEFAULT NULL,
`MemberGender` char(1) DEFAULT NULL,
`MemberZip` varchar(15) DEFAULT NULL,
`POS1` varchar(6) DEFAULT NULL,
`POS2` varchar(6) DEFAULT NULL,
`POS3` varchar(6) DEFAULT NULL,
`Prim_Diag` varchar(4) DEFAULT NULL,
`ICD_FLAG` varchar(5) DEFAULT NULL,
`Diag1` varchar(30) DEFAULT NULL,
`Diag2` varchar(30) DEFAULT NULL,
`Diag3` varchar(30) DEFAULT NULL,
`Diag4` varchar(30) DEFAULT NULL,
`Diag5` varchar(30) DEFAULT NULL,
`Diag6` varchar(30) DEFAULT NULL,
`Diag7` varchar(30) DEFAULT NULL,
`Diag8` varchar(30) DEFAULT NULL,
`Diag9` varchar(30) DEFAULT NULL,
`Diag10` varchar(30) DEFAULT NULL,
`Diag11` varchar(30) DEFAULT NULL,
`Diag12` varchar(30) DEFAULT NULL,
`Diag13` varchar(30) DEFAULT NULL,
`Diag14` varchar(30) DEFAULT NULL,
`Diag15` varchar(30) DEFAULT NULL,
`Diag16` varchar(30) DEFAULT NULL,
`Diag17` varchar(30) DEFAULT NULL,
`Diag18` varchar(30) DEFAULT NULL,
`Diag19` varchar(30) DEFAULT NULL,
`Diag20` varchar(30) DEFAULT NULL,
`Diag21` varchar(30) DEFAULT NULL,
`Diag22` varchar(30) DEFAULT NULL,
`Diag23` varchar(30) DEFAULT NULL,
`Diag24` varchar(30) DEFAULT NULL,
`Diag25` varchar(30) DEFAULT NULL,
`Diag1_POA_Ind` varchar(1) DEFAULT NULL,
`Diag2_POA_Ind` varchar(1) DEFAULT NULL,
`Diag3_POA_Ind` varchar(1) DEFAULT NULL,
`Diag4_POA_Ind` varchar(1) DEFAULT NULL,
`Diag5_POA_Ind` varchar(1) DEFAULT NULL,
`Diag6_POA_Ind` varchar(1) DEFAULT NULL,
`Diag7_POA_Ind` varchar(1) DEFAULT NULL,
`Diag8_POA_Ind` varchar(1) DEFAULT NULL,
`Diag9_POA_Ind` varchar(1) DEFAULT NULL,
`Diag10_POA_Ind` varchar(1) DEFAULT NULL,
`Diag11_POA_Ind` varchar(1) DEFAULT NULL,
`Diag12_POA_Ind` varchar(1) DEFAULT NULL,
`Diag13_POA_Ind` varchar(1) DEFAULT NULL,
`Diag14_POA_Ind` varchar(1) DEFAULT NULL,
`Diag15_POA_Ind` varchar(1) DEFAULT NULL,
`Diag16_POA_Ind` varchar(1) DEFAULT NULL,
`Diag17_POA_Ind` varchar(1) DEFAULT NULL,
`Diag18_POA_Ind` varchar(1) DEFAULT NULL,
`Diag19_POA_Ind` varchar(1) DEFAULT NULL,
`Diag20_POA_Ind` varchar(1) DEFAULT NULL,
`Diag21_POA_Ind` varchar(1) DEFAULT NULL,
`Diag22_POA_Ind` varchar(1) DEFAULT NULL,
`Diag23_POA_Ind` varchar(1) DEFAULT NULL,
`Diag24_POA_Ind` varchar(1) DEFAULT NULL,
`Diag25_POA_Ind` varchar(1) DEFAULT NULL,
`APR_DRG_Code` varchar(6) DEFAULT NULL,
`Inst_Bill_Type` varchar(3) DEFAULT NULL,
`ICD_Proc1` varchar(30) DEFAULT NULL,
`ICD_Proc2` varchar(30) DEFAULT NULL,
`ICD_Proc3` varchar(30) DEFAULT NULL,
`ICD_Proc4` varchar(30) DEFAULT NULL,
`ICD_Proc5` varchar(30) DEFAULT NULL,
`ICD_Proc6` varchar(30) DEFAULT NULL,
`ICD_Proc7` varchar(30) DEFAULT NULL,
`ICD_Proc8` varchar(30) DEFAULT NULL,
`ICD_Proc9` varchar(30) DEFAULT NULL,
`ICD_Proc10` varchar(30) DEFAULT NULL,
`ICD_Proc11` varchar(30) DEFAULT NULL,
`ICD_Proc12` varchar(30) DEFAULT NULL,
`ICD_Proc13` varchar(30) DEFAULT NULL,
`ICD_Proc14` varchar(30) DEFAULT NULL,
`ICD_Proc15` varchar(30) DEFAULT NULL,
`ICD_Proc16` varchar(30) DEFAULT NULL,
`ICD_Proc17` varchar(30) DEFAULT NULL,
`ICD_Proc18` varchar(30) DEFAULT NULL,
`ICD_Proc19` varchar(30) DEFAULT NULL,
`ICD_Proc20` varchar(30) DEFAULT NULL,
`ICD_Proc21` varchar(30) DEFAULT NULL,
`ICD_Proc22` varchar(30) DEFAULT NULL,
`ICD_Proc23` varchar(30) DEFAULT NULL,
`ICD_Proc24` varchar(30) DEFAULT NULL,
`ICD_Proc25` varchar(30) DEFAULT NULL,
`ICD_ProcDate1` date DEFAULT NULL,
`ICD_ProcDate2` date DEFAULT NULL,
`ICD_ProcDate3` date DEFAULT NULL,
`ICD_ProcDate4` date DEFAULT NULL,
`ICD_ProcDate5` date DEFAULT NULL,
`ICD_ProcDate6` date DEFAULT NULL,
`ICD_ProcDate7` date DEFAULT NULL,
`ICD_ProcDate8` date DEFAULT NULL,
`ICD_ProcDate9` date DEFAULT NULL,
`ICD_ProcDate10` date DEFAULT NULL,
`ICD_ProcDate11` date DEFAULT NULL,
`ICD_ProcDate12` date DEFAULT NULL,
`ICD_ProcDate13` date DEFAULT NULL,
`ICD_ProcDate14` date DEFAULT NULL,
`ICD_ProcDate15` date DEFAULT NULL,
`ICD_ProcDate16` date DEFAULT NULL,
`ICD_ProcDate17` date DEFAULT NULL,
`ICD_ProcDate18` date DEFAULT NULL,
`ICD_ProcDate19` date DEFAULT NULL,
`ICD_ProcDate20` date DEFAULT NULL,
`ICD_ProcDate21` date DEFAULT NULL,
`ICD_ProcDate22` date DEFAULT NULL,
`ICD_ProcDate23` date DEFAULT NULL,
`ICD_ProcDate24` date DEFAULT NULL,
`ICD_ProcDate25` date DEFAULT NULL,
`Cond_Code1` varchar(90) DEFAULT NULL,
`Cond_Code2` varchar(90) DEFAULT NULL,
`Cond_Code3` varchar(90) DEFAULT NULL,
`Cond_Code4` varchar(90) DEFAULT NULL,
`Cond_Code5` varchar(90) DEFAULT NULL,
`Cond_Code6` varchar(90) DEFAULT NULL,
`Cond_Code7` varchar(90) DEFAULT NULL,
`Cond_Code8` varchar(90) DEFAULT NULL,
`Cond_Code9` varchar(90) DEFAULT NULL,
`Cond_Code10` varchar(90) DEFAULT NULL,
`Cond_Code11` varchar(90) DEFAULT NULL,
`Admit_Date` date DEFAULT NULL,
`Auth_Number` varchar(16) DEFAULT NULL,
`Admit_Code` varchar(6) DEFAULT NULL,
`Admit_Hr` varchar(50) DEFAULT NULL,
`Discharge_Hr` varchar(50) DEFAULT NULL,
`Claim_PtStat` char(2) DEFAULT NULL,
`Claimline_ResStat` char(1) DEFAULT NULL,
`Claimline_ProcessStat` char(1) DEFAULT NULL,
`Claim_Kind` varchar(8) DEFAULT NULL,
`Quantity` varchar(20) DEFAULT NULL,
`Total_Billed` varchar(20) DEFAULT NULL,
`Billed_Amt` varchar(20) DEFAULT NULL,
`RX_NDC` varchar(20) DEFAULT NULL,
`RX_GenericFlag` varchar(20) DEFAULT NULL,
`RX_DaySupply` varchar(20) DEFAULT NULL,
`RX_DispFee` varchar(20) DEFAULT NULL,
`RX_IngredCost` varchar(20) DEFAULT NULL,
`RX_FormFlag` varchar(20) DEFAULT NULL,
`RX_DateWrit` varchar(20) DEFAULT NULL,
`RX_BrandName` varchar(20) DEFAULT NULL,
`RX_DrugStrength` varchar(20) DEFAULT NULL,
`RX_GPI` varchar(20) DEFAULT NULL,
`RX_GPIDesc` varchar(20) DEFAULT NULL,
`RX_ControlledFlag` varchar(20) DEFAULT NULL,
`RX_CompoundCode` varchar(20) DEFAULT NULL,
`Claimline_NetPaid` varchar(20) DEFAULT NULL,
`Claim_OrigNum` varchar(36) DEFAULT NULL,
`Claim_Status` char(1) DEFAULT NULL,
`Plan_Desc` varchar(240) DEFAULT NULL,
`Payer` varchar(8) DEFAULT NULL,
`YearMonth` varchar(8) DEFAULT NULL,
`Orig_Filename` varchar(50) DEFAULT NULL,
PRIMARY KEY (`United_Clm_Key`)
) ENGINE=InnoDB AUTO_INCREMENT=2330 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Here is the other table. I am able to import a file into it. A typical file has about 30,000 rows.
CREATE TABLE `united_members` (
`United_Elig_Key` smallint NOT NULL AUTO_INCREMENT,
`COMPANY_CODE` varchar(5) DEFAULT NULL,
`LOB` varchar(30) DEFAULT NULL,
`MemberCIN` varchar(20) DEFAULT NULL,
`MemberMedicareID` varchar(12) DEFAULT NULL,
`MemberSubscriberID` varchar(14) DEFAULT NULL,
`MemberFName` varchar(30) DEFAULT NULL,
`MemberMI` char(1) DEFAULT NULL,
`MemberLName` varchar(35) DEFAULT NULL,
`MemberGender` char(1) DEFAULT NULL,
`MemberDOB` date DEFAULT NULL,
`MemberAddr1` varchar(55) DEFAULT NULL,
`MemberAddr2` varchar(55) DEFAULT NULL,
`MemberCity` varchar(30) DEFAULT NULL,
`MemberState` varchar(6) DEFAULT NULL,
`MemberZip` varchar(15) DEFAULT NULL,
`ProviderIDInternal` varchar(17) DEFAULT NULL,
`ProviderNPI` varchar(10) DEFAULT NULL,
`MedicarePIN` varchar(15) DEFAULT NULL,
`ProviderFullName` varchar(75) DEFAULT NULL,
`MemberPhoneHome` varchar(20) DEFAULT NULL,
`MemberRisk` varchar(20) DEFAULT NULL,
`ProviderTIN` varchar(9) DEFAULT NULL,
`EligStart` date DEFAULT NULL,
`EligEnd` date DEFAULT NULL,
`DIAMOND_ID` varchar(14) DEFAULT NULL,
`EthnicCodeDesc` varchar(35) DEFAULT NULL,
`Practice` varchar(50) DEFAULT NULL,
`Payer` varchar(8) DEFAULT NULL,
`YearMonth` varchar(8) DEFAULT NULL,
`Orig_Filename` varchar(50) DEFAULT NULL,
PRIMARY KEY (`United_Elig_Key`)
) ENGINE=InnoDB AUTO_INCREMENT=29271 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
- a few sample rows from the .csv file from which you're importing
Unfortunately, I'm unable to give you all the data because it is all protected health information of our members. But here are some rows with all identifying information replaced with xxxxx.
Here are some rows from the united_claims table:
United_Clm_Key COMPANY_CODE LOB Svc_Type SEQ_CLAIM_ID Claim_Number Claimline_Number Claimline_AdjFlag Claim_SvcBeginDate Claim_SvcEndDate Claim_PaidDate Claim_PrintDate CHECK_NUMBER Claimline_ProcCode Claimline_Mod1 Claimline_Mod2 Claimline_Mod3 Claimline_Mod4 Claimline_RevCode Claimline_RecDate Claimline AdjudDate Claimline_SvcBeginDate Claimline_SvcEndDate Svc_Prov_ID Svc_Prov_FullName Svc_Prov_NPI Svc_Prov_SpecCode Svc_Prov_Type Svc_Prov_ParStat Att_Prov_ID Att_Prov_FullName Att_Prov_NPI Ref_Prov_ID Ref_Prov_FullName Vendor_ID Vendor_FullName Vendor_TaxID Member_SubscriberID PAT_CONTROL_NO Member_DOB Member_Lname Member_Mname Member_Fname Member_MedicaidID Member_MedicareID Member_Gender Member_Zip POS1 POS2 POS3 Prim_Diag ICD_FLAG Diag1 Diag2 Diag3 Diag4 Diag5 Diag6 Diag7 Diag8 Diag9 Diag10 Diag11 Diag12 Diag13 Diag14 Diag15 Diag16 Diag17 Diag18 Diag19 Diag20 Diag21 Diag22 Diag23 Diag24 Diag25 Diag1_POA_Ind Diag2_POA_Ind Diag3_POA_Ind Diag4_POA_Ind Diag5_POA_Ind Diag6_POA_Ind Diag7_POA_Ind Diag8_POA_Ind Diag9_POA_Ind Diag10_POA_Ind Diag11_POA_Ind Diag12_POA_Ind Diag13_POA_Ind Diag14_POA_Ind Diag15_POA_Ind Diag16_POA_Ind Diag17_POA_Ind Diag18_POA_Ind Diag19_POA_Ind Diag20_POA_Ind Diag21_POA_Ind Diag22_POA_Ind Diag23_POA_Ind Diag24_POA_Ind Diag25_POA_Ind APR_DRG_Code Inst_Bill_Type ICD_Proc1 ICD_Proc2 ICD_Proc3 ICD_Proc4 ICD_Proc5 ICD_Proc6 ICD_Proc7 ICD_Proc8 ICD_Proc9 ICD_Proc10 ICD_Proc11 ICD_Proc12 ICD_Proc13 ICD_Proc14 ICD_Proc15 ICD_Proc16 ICD_Proc17 ICD_Proc18 ICD_Proc19 ICD_Proc20 ICD_Proc21 ICD_Proc22 ICD_Proc23 ICD_Proc24 ICD_Proc25 ICD_ProcDate1 ICD_ProcDate2 ICD_ProcDate3 ICD_ProcDate4 ICD_ProcDate5 ICD_ProcDate6 ICD_ProcDate7 ICD_ProcDate8 ICD_ProcDate9 ICD_ProcDate10 ICD_ProcDate11 ICD_ProcDate12 ICD_ProcDate13 ICD_ProcDate14 ICD_ProcDate15 ICD_ProcDate16 ICD_ProcDate17 ICD_ProcDate18 ICD_ProcDate19 ICD_ProcDate20 ICD_ProcDate21 ICD_ProcDate22 ICD_ProcDate23 ICD_ProcDate24 ICD_ProcDate25 Cond_Code1 Cond_Code2 Cond_Code3 Cond_Code4 Cond_Code5 Cond_Code6 Cond_Code7 Cond_Code8 Cond_Code9 Cond_Code10 Cond_Code11 Admit_Date Auth_Number Admit_Code Admit_Hr Discharge_Hr Claim_PtStat Claimline_ResStat Claimline_ProcessStat Claim_Kind Quantity Total_Billed Billed_Amt RX_NDC RX_GenericFlag RX_DaySupply RX_DispFee RX_IngredCost RX_FormFlag RX_DateWrit RX_BrandName RX_DrugStrength RX_GPI RX_GPIDesc RX_ControlledFlag RX_CompoundCode Claimline_NetPaid Claim_OrigNum Claim_Status Plan_Desc Payer YearMonth Orig_Filename
NULL UHGNY NY Medicaid OUTPATIENT -32011300000000 20K335294401 60 2020-11-03 2020-11-04 2021-04-21 2021-04-21 0 J1953 N/A N/A N/A N/A 636 2021-04-15 2021-04-17 2020-11-04 2020-11-04 107845001 NYP-COLUMBIA 1194832477 OTHER Acute Care Hosp P 3536231002 THAKUR, KIRAN T. 1982861167 N/A N/A 107845501 NYP-COLUMBIA 133957095 xxxxx HW50000761932005 xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx N/A N/A N/A R51 0 R51.9 N/A G40.901 Z86.61 N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A NO_APR 137 N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A 9999-09-09 1 7 D P MED-UB 0 24077.02 9.06 51224001301 - 0 0 - 9999-09-09 LEVETIRACETAM 500 mg N/A N/A 0 0 20K335294400 D NY Medicaid 21+ United 202105 CHIPA_202105_CLM_CAID
NULL UHGNY NY Medicaid INPATIENT -32103000000000 21B964160700 27 2021-01-11 2021-02-19 2021-03-13 2021-03-13 0 N/A N/A N/A N/A N/A 460 2021-02-24 2021-03-09 2021-01-11 2021-02-19 107845001 NYP-COLUMBIA 1194832477 OTHER Acute Care Hosp P 6435629002 GOLDSHTROM, NIMROD 1205148160 N/A N/A 107845501 NYP-COLUMBIA 133957095 xxxxx HW50000897982200 xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx N/A N/A N/A Q21 0 Q21.0 J69.0 P35.1 Z99.11 I27.20 J96.11 I74.5 D69.6 Q32.0 I50.9 Q25.0 Q21.1 K22.2 J39.8 Q90.9 J98.09 I27.21 N13.30 K21.9 Z93.1 E87.1 E86.0 B01.9 B37.49 B96.5 N Y Y Y N N Y Y Y Y Y Y Y N N N N N 956 111 02Q50ZZ 5A1221Z 02LR0ZT 02UM0JZ 0BH17EZ 5A1955Z N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A 2021-02-05 2021-02-05 2021-02-05 2021-02-05 2021-01-13 2021-01-13 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 9999-09-09 N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A 2021-01-11 9 18 6 70 D P MED-UB 0 1385922.99 27163 N/A - 0 0 - 9999-09-09 N/A N/A N/A 0 D NY Medicaid 0-20 yrs United 202105 CHIPA_202105_CLM_CAID
Here are some rows from the united_members table
United_Elig_Key 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
NULL UHGNY NY Medicaid xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx 6267822001 1568770238 6267822 BECHT, JOAN E. 6317223968 1.0243 132828349 2019-08-17 9999-12-31 12534994 Caucasian Sun River Health United 202106 CHIPA_202106_MEM_CAID
NULL UHGNY NY Medicaid xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx xxxxx 688858003 1083616866 688858 SCHILLER, ROBERT M. 3478378324 0.1929 133273402 2020-09-01 9999-12-31 12909734 Caucasian Institute for Family Health United 202106 CHIPA_202106_MEM_CAID
- the Load Data command you are using: I am using the Table Data Import Wizard
- the result of this query: select @@innodb_buffer_pool_size;
'25769803776'
I really can't thank you enough for your help.
Thanks,
Kimber