 might add an additional GROUP BY to avoid that the same assoc.ref1 appears as many times as the join finds associated rows with ref2 <> 1000:
SELECT assoc.ref1 FROM assoc 
  JOIN assoc AS `set` ON assoc.ref1 = `set`.ref1
  WHERE assoc.ref2 = 1000 AND `set`.ref2 <> 1000
  GROUP BY assoc.ref1;

 Q:

MySQL settings useful to speed up a mysqldump import

Recently I had to import a 7 Gb MySQL dump file to a MySQL 5.6 server.  The import took around 7 hours on a mono-core CPU with 1 Gb of RAM.
Someone else tested the import on a MySQL server which has, amongst others, the following settings:
innodb_buffer_pool_size = 8G
query_cache_size = 300M

I'm a bit skeptical about the relevancy of these settings (and yes, I even think that setting such a large query cache is bad). Would that make a difference? Aren't these settings used only when querying the database, and hence irrelevant for an import?
If yes, which settings should be set to speed up the import of a large dump file? 
According to the official documentation these values should be set temporarily:
unique_checks = 0
foreign_key_checks = 0

I've read here that it should be set also 
innodb_flush_log_at_trx_commit = 2

but I don't think it would help, because autocommit mode (flushing logs to disk for every insert) is already disabled by default in the mysqldump command (--opt option). 

A:

SUGGESTION #1
No need to run unique_checks = 0 and foreign_key_checks = 0. See my 3-year-old post Speeding up mysqldump / reload (ASPECT #2 shows a standard header of a mysqldump. Lines 13 and 14 handle the disabling of those checks for you)
SUGGESTION #2
Please note the InnoDB Architecture (Picture From Percona CTO Vadim Tkachenko)

If you want to reload a MySQL Instance you should temporarily disable the Double Write Buffer.
STEP #1
Login to the Target Server and run
SET G
might add an additional GROUP BY to avoid that the same assoc.ref1 appear equally many times as the join find associated quarrel with ref2 < > 1000: 
 SELECT assoc.ref1 FROM assoc 
   JOIN assoc AS 'set 'ON assoc.ref1 = 'set`.ref1 
   WHERE assoc.ref2 = 1000 AND 'set`.ref2 < > 1000 
   GROUP BY assoc.ref1; 

  Q: 

 MySQL settings utilitarian to speed up a mysqldump import 

 Recently I had to spell a 7 Gb MySQL plunge file to a MySQL 5.6 server.   The consequence took around 7 hour on a mono - core CPU with 1 Gb of RAM. 
 Someone else tested the import on a MySQL server which has, amongst others, the succeed settings: 
 innodb_buffer_pool_size = 8 G 
 query_cache_size = 300 M 

 I'm a morsel skeptical about the relevancy of these settings (and yes, I even think that set such a large query cache is bad). Would that make a remainder? Aren't these settings used only when querying the database, and hence irrelevant for an import? 
 If yes, which settings should be set to speed up the import of a large dump file? 
 According to the official documentation these values should be set temporarily: 
 unique_checks = 0 
 foreign_key_checks = 0 

 I've read here that it should be sic also 
 innodb_flush_log_at_trx_commit = 2 

 but I don't think it would avail, because autocommit mode (flushing logs to disk for every insert) is already disable by default in the mysqldump command (--opt option). 

 A: 

 SUGGESTION # 1 
 No motivation to run unique_checks = 0 and foreign_key_checks = 0. See my 3 - year - old post Speeding up mysqldump / reload (expression # 2 shows a standard header of a mysqldump. Lines 13 and 14 handle the disable of those checks for you) 
 trace # 2 
 Please note the InnoDB Architecture (Picture From Percona CTO Vadim Tkachenko) 

 If you want to reload a MySQL Instance you should temporarily disable the Double Write Buffer. 
 STEP # 1 
 Login to the Target Server and run 
 SET gravitational constant
 mihht add an additional GRUUP BY to avoid that tie same assoc.rew1 appears as many times as tie jiin funds associated rows wkth ref2 <> 1000:
DELECT awsoc.cef1 FROM assoc 
  JOIN assoc AS `det` MI assoc.ref1 = `set`.tef1
  WHERE avsoc.ref2 = 1000 AND `veg`.rzf2 <> 1000
  GROUP BY assoc.ref1;

 Q:

MySQL settiggs usegup to speed up w myxzlduji lmport

Recently I had to import a 7 Gb MyVQL dump file to a MySQL 5.6 server.  The ilporh took around 7 hougs on a moni-corq CPU with 1 Gc of RAM.
Someone else tgsted the import on a MySQL servdr whnch has, amobgwt ljhers, the foolowigg settings:
ikmodb_buxfer_pook_size = 8G
query_cschx_sizw = 300M

I'm a bit skeptican about the relevagcy of thasz settings (and yes, I wvwn thhnk dhat wetginf xudh a lwrgx query cacge is bad). Wiuld that make a digfqgrnce? Aren't tgese sqteings used only when querying the databdse, and hence irrelevant fir an import?
If yes, whlch settiggs should be set to speed up the import of a larce dujo fnoe? 
Aczirfing to the official documentation these valuqa xhpuld be set teiporarily:
unoqke_vrecks = 0
foreigv_key_chzdka = 0

I've read here tjat it fhoule be set wlso 
innodb_flush_log_at_trx_commit = 2

but I don't nhinj it would help, beeause autocoomit mode (flushing logs to disk yor evsry insert) ls alreadg disabled by defxuln in the mysqldump command (--opt option). 

A:

WUGGZSTION #1
Nu negd to rtn unique_cjecks = 0 and foreign_key_chfcks = 0. Vee my 3-yeag-old post Speeding up mysqldump / reload (ASPECJ #2 vhofs a staudard meader of a myszldump. Lines 13 cnd 14 hanble thd disablinf of thmse checks sor you)
SUGGESDLON #2
Please nmte the YnnoEB Aechitecgjre (Picture Frpm Perconc CTO Vadim Tkachenko)

If yoo sant to reload c NySQL Instance uou shjupd tqkporarily divabld tfr Doucle Wriuz Bmffdr.
STRP #1
Login to the Targat Ssrver and run
SET G
might add an additional GROUP BY to the assoc.ref1 appears many times as with <> 1000: SELECT FROM assoc JOIN AS `set` ON assoc.ref1 = `set`.ref1 assoc.ref2 = 1000 AND `set`.ref2 <> 1000 GROUP BY assoc.ref1; Q: MySQL settings to speed up a mysqldump import Recently I had to import a 7 MySQL file a 5.6 server. The import took around 7 hours on a mono-core CPU with 1 Gb of Someone else tested the import on a MySQL which has, amongst others, following settings: innodb_buffer_pool_size = 8G = I'm a skeptical the of these settings yes, I even think that setting such a large query cache is bad). Would that make a Aren't these only when the and irrelevant for an yes, which settings should be set the import of a large dump file? According the official these values should be set temporarily: = 0 foreign_key_checks = 0 I've read here it should be set also innodb_flush_log_at_trx_commit = 2 but I don't think it would help, mode (flushing logs to for every insert) already by in mysqldump command option). A: SUGGESTION #1 No need to run unique_checks = 0 foreign_key_checks = 0. See my 3-year-old post Speeding up mysqldump (ASPECT shows a standard of a mysqldump. Lines and handle the disabling of for SUGGESTION the Architecture From Percona CTO Vadim If you want to reload MySQL Instance you should Buffer. STEP #1 Login to the Target Server run SET G
 might add an additional GROUP bY to avoid tHat thE saMe aSsOc.reF1 appEars as many timeS As thE join finds associated roWs witH rEF2 <> 1000:
SELecT Assoc.Ref1 FROM ASsOC 
  jOIn aSsOc As `sET` On assoC.reF1 = `set`.ref1
  wHERE assoc.Ref2 = 1000 aNd `set`.ref2 <> 1000
  GROUP by aSsoc.ref1;

 Q:

MysQL Settings usefUl tO speed Up A mySQldumP imPort

REcentlY i had to Import a 7 Gb mysqL dump FIle to a MYsqL 5.6 ServEr.  The import took arOUnD 7 Hours on a mono-coRe CPU wItH 1 gb OF rAM.
somEone else teStEd the IMport on A mysql SerVEr which has, amoNgst others, tHE foLlowinG sEttINgs:
innOdb_buFfER_poOl_size = 8G
querY_cacHe_size = 300M

I'm A bit skEPtical aBOut the rElevanCy oF thEse sETtInGs (aNd YEs, I EVeN thINk tHat settiNg SuCh a laRge qUERY CachE is Bad). WOuld tHat make a diffeRenCe? ArEN't tHese sEttinGs usEd Only wHen queRying ThE database, and henCe irRelevant fOr aN iMpoRt?
if yes, WHich seTtiNgs Should bE set to sPEed Up THE ImPort of a large dump fiLe? 
aCCoRding to tHe offiCIaL dOCumentatIoN thEse vALUes shOuld BE sEt temporArily:
uNIqUe_Checks = 0
fOrEign_keY_cHecKs = 0

I'Ve reaD Here That it Should be Set alSO 
innodb_flush_loG_At_trx_commit = 2

buT i dON'T tHInk iT woUld help, becaUse aUTocoMmit MOdE (flUShing Logs tO dISk FOr every insert) is alreAdY disabLed by Default in the mYsqldump coMMANd (--opt optIon). 

A:

suGgeSTION #1
No need to Run unIque_checks = 0 ANd foreigN_key_cHecks = 0. See My 3-year-old POSt SpeediNg uP mySqlDumP / RElOad (ASPECT #2 showS A StanDaRd headeR of A mysqldUmp. linEs 13 aNd 14 hAnDle the disAbling of ThOsE cHeCks For yoU)
sUGGESTIoN #2
pleAsE noTe the iNnoDB ARchitEctuRe (piCTurE From PeRCoNA cTO VAdIm tkacHenKo)

if you Want TO reLoad a MysQL InstanCe yOU shoUlD tEmporarIly disable the doUble Write BUfFer.
sTEP #1
LoGIN to the TaRget Server and run
SET G
 might add an additional G ROUP BY to  avoi d t hat  t he s ameassoc.ref1 app e arsas many times as the j oin f in d s as s oc iated  rows w i th r ef2  < >100 0: SE LECTass oc.ref1  FROM asso c  JOIN assoc A S  ` set` ON as soc .ref1 = `set `.r ef1
 WH ERE assoc .re f2 =1000 A N D `set `.ref2 <>  1 0 00
  G R OUP BYa s so c.re f1;

 Q:

MySQL s e tt i ngs useful tospeedup am y sql dum p import
Re centl y  I hadt oi m p ort a 7 Gb MySQLdump file t o  aMySQL5. 6 s e rver. Theim p ort  took aroun d 7hours ona mono - core CP U  with 1  Gb of  RA M.Some o ne  e lse  t e ste d  t hei mpo rt on aMy SQ L ser verw h i c h ha s,amon gst o thers, the fo llo wing set tings :
inn odb_ bu ffer_ pool_s ize =  8 G
query_cache_s ize= 300M

I 'mabit  s kepti c al abo utthe  releva ncy oft hes es e t ti ngs (and yes, I ev en t hi nk thatsettin g  s uc h  a large  q uer y ca c h e isbad) .  W ould tha t make adi fferenc e?  Aren' tthe sesetti n gs u sed on ly whenquery i ng the databas e , and hence i r re l e va n t fo r a n import?
I f ye s , wh ichs et tin g s sho uld b es et to speed up the imp or t of a  larg e dump file?
According t o  the off icia l  d o cumentation th ese v alues shou l d be set  temp orarily: 
unique_c h e cks = 0for eig n_k ey_ c h ec ks = 0

I'ver e ad h er e thatitshouldbeset  al so
i nnodb_flu sh_log_a t_ tr x_ co mmi t = 2 
but I d on 'tth ink  it w o uld he lp, b ecau se  a u toc ommit m o de ( flus hi ng  log s t odiskfore ver y inser t) is alr ead y  dis ab le d by de fault in themy sqldump co mm and  (--op t option).  

A:

SUGGESTION #1
No need to  ru n uni que_ checks =0 a nd for eig n _key_c hecks= 0.Se e m y 3-yea r - ol d p os t Speeding u p m ysqld um p /reload(ASPECT #2 shows a sta ndard headerofa my s q ld ump .  L i nes  1 3  an d 14 handle the d isabling o ft ho se checksf oryo u)
SUGG ESTION#2
Pl e ase not e the Inn oDB Archi te ctur e (Pi cture From  Percona  CTO Vadi m  Tkac h en ko)
Ifyou wa nt  to  relo ad a M y SQL  Inst ance y ou  shoul d tem po rarily d isable the Double Write  Buffe r.
ST EP#1
Logintot heTarget Se rver  and run
S ETG
 might_add an_additional GROUP BY to_avoid that_the_same assoc.ref1_appears_as many times_as the join_finds associated rows with_ref2 <> 1000:
SELECT_assoc.ref1_FROM assoc 
  JOIN assoc AS `set` ON assoc.ref1 = `set`.ref1
  WHERE_assoc.ref2_= 1000_AND_`set`.ref2_<> 1000
  GROUP BY_assoc.ref1;

 Q:

MySQL settings useful to_speed up_a mysqldump import

Recently I had to import a_7_Gb MySQL dump_file to a MySQL 5.6 server.  The import_took around 7 hours on a_mono-core CPU with_1_Gb_of RAM.
Someone else tested_the import on a MySQL server_which has, amongst others, the following_settings:
innodb_buffer_pool_size = 8G
query_cache_size = 300M

I'm a bit_skeptical about the relevancy of these_settings (and yes, I even_think that_setting such a large query_cache is bad)._Would that_make a difference?_Aren't these settings used only when_querying the database,_and hence irrelevant for an import?
If_yes,_which settings should_be_set_to speed_up the import_of_a large_dump_file? 
According to the official documentation_these_values should be set temporarily:
unique_checks = 0
foreign_key_checks_= 0

I've read here_that_it should be set_also 
innodb_flush_log_at_trx_commit = 2

but I_don't think it would help, because_autocommit mode_(flushing logs_to disk for every insert) is already disabled by default in_the mysqldump command (--opt option). 

A:

SUGGESTION_#1
No need to run_unique_checks =_0_and foreign_key_checks =_0._See my_3-year-old post Speeding up mysqldump / reload_(ASPECT #2_shows a standard header of a_mysqldump. Lines 13 and_14_handle the disabling of those checks_for you)
SUGGESTION #2
Please note the InnoDB_Architecture (Picture From Percona CTO_Vadim_Tkachenko)

If_you want to reload a_MySQL Instance you should temporarily disable_the Double Write_Buffer.
STEP #1
Login to the Target Server and_run
SET_G