开发者

Does the index need rebuilding when not batch inserting data?

开发者 https://www.devze.com 2023-02-27 17:35 出处:网络
Let me rephrase the question: Is it vital for the /*! */ lines, in the SQL file below, to exist when inserting data using individual INSERT statements?

Let me rephrase the question:

Is it vital for the /*! */ lines, in the SQL file below, to exist when inserting data using individual INSERT statements?

This is the contents of the file now:

LOCK TABLES `adv_links` WRITE;
/*!40000 ALTER TABLE `adv_links` DISABLE KEYS */;
INSERT INTO `adv_links` VALUES (13,'photoreal','http://w.otoreal.tk','',14,'something','something, something',64,0,1,1,'',0开发者_开发百科,'',1277754393,1277754393,0,0,0,'85.72.100.18',0,0,0,'');
INSERT INTO `adv_links` VALUES (4,'something cirocco', 'ttp://ww.roccoh.t/','',16,'something.','',86,0,1,1,'',0,'achille',1259843781,1259843781,2714,0,0,'91.132.75.249',0,0,0,'');
INSERT INTO `adv_links` VALUES (5,'somethingasco','htp://w.vsco.b/ge/gtpage.asp?i=0','',16,'somethingsco','sco',113,0,1,1,'',0,'achille',1259843935,1259843935,2714,0,0,'91.132.75.249',0,0,0,'');
INSERT INTO `adv_links` VALUES (6,'something','http://w.ja.b/Default.aspx','',16,'somethingaga','',62,0,1,1,'',0,'achille',1259848860,1259848860,2714,0,0,'91.132.75.249',0,0,0,'');
INSERT INTO `adv_links` VALUES (7,'something','htp://w.arkorrier.com.tr/English/AC_urun.asp?ID=ACU36#','',16,'','',90,0,1,1,'',0,'achille',1259848962,1259848962,2714,0,0,'91.132.75.249',0,0,0,'');
-- omitted
/*!40000 ALTER TABLE `adv_links` ENABLE KEYS */;
UNLOCK TABLES;

I am asking this because when reaching the /*!40000 ALTER TABLE adv_link ENABLE KEYS */; line, MySQL goes and repairs the index by sort and it can't find enough space since the dataset is large enough to choke in the tmpdir while sorting.


If you don't disable and re-enable your keys, the keys will update dynamically with each INSERT statement (as they normally do).

This does not require writes to tmpdir, but is more slow.

You may drop these lines if you cannot increase temporary space used by MySQL, though it will slow down your import.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号