Constant connection timeouts- error 2013
I am using the most current Mysql community server and workbench on my Macbook pro. Just installed a couple of days ago.
Most works fine until I need to join some large tables (like 5-10M rows in each table). I get the 2013 lost connection error. I actually have a few that are a little larger to do after this.
I have done a few things to prevent this error.
1) Added indexes to the variables that are in my WHERE and GROUP BY by statements (ex-ALTER TABLE Prof_claimline_new ADD INDEX indx_Claim_ID (CLM_ID);).
2) Increased my timeouts:
SET GLOBAL net_read_timeout=1200;
SET GLOBAL net_write_timeout=1200;
3) This seems to have fixed and sped up the smaller queries (1M)- but for the larger tables I am still getting a lost connection at 600 sec (even though the timeout is set to 1200).
Anything I can do to speed up the query so it does not get to the 600 sec point OR keep it from dropping at 600 sec? Don't care which- just want my queries to work. Been driving me crazy for a couple of days.
Thanks so much for insight.
(I think it is a pretty straight forward query- but I will paste the code in case I did something dumb. But it seems to be occurring on other queries on these same tables):
CREATE TABLE Prof_Master_PCB
SELECT `PROF_CLM_HDR.PCB`.CLM_ID,
`PROF_CLM_HDR.PCB`.FIRST_DT_OF_SVC,
`PROF_CLM_HDR.PCB`.LAST_DT_OF_SVC,
`PROF_CLM_HDR.PCB`.MBR_ID,
`PROF_CLM_HDR.PCB`.PATN_AGE_IN_YR,
`PROF_CLM_HDR.PCB`.PROD,
`PROF_CLM_HDR.PCB`.BLUE_CARD_ID,
`PROF_CLM_HDR.PCB`.ALW_AMT,
`PROF_CLM_HDR.PCB`.COB_IN,
`PROF_CLM_HDR.PCB`.NTWK,
`PROF_CLM_HDR.PCB`.PROD_SH_NM,
`PROF_CLM_HDR.PCB`.PROV_ID,
Prof_claimline_new.CLM_LN,
Prof_claimline_new.POS,
Prof_claimline_new.PROC_CD,
Prof_claimline_new.MOD_1,
Prof_claimline_new.MOD_2,
Prof_claimline_new.MOD_3,
Prof_claimline_new.MOD_4
FROM `PROF_CLM_HDR.PCB`, Prof_claimline_new
WHERE `PROF_CLM_HDR.PCB`.CLM_ID=Prof_claimline_new.CLM_ID
GROUP BY `PROF_CLM_HDR.PCB`.CLM_ID;
Edited 1 time(s). Last edit at 05/30/2012 12:31AM by Myles Gartland.