You can use "disable Key" before loading data & use "enable Key " after loading
Command :- alter table {table. Name } disable key; Same enabling DK Sent from Phone On 27-Jun-2013, at 4:57, nixofortune <nixofort...@gmail.com> wrote: > This is my table: > CREATE TABLE `ga_monthly_keyword_visits` ( > `site_id` int(11) DEFAULT NULL, > `index_date` int(11) DEFAULT NULL, > `index_month` int(11) NOT NULL, > `index_year` int(11) NOT NULL, > `keyword` varchar(128) DEFAULT NULL, > `source` varchar(30) DEFAULT NULL, > `visits` int(11) DEFAULT NULL, > `bounced_visits` int(11) DEFAULT NULL, > `transactions` int(11) DEFAULT NULL, > `revenue` float(10,2) DEFAULT NULL, > `value_per_click` float(10,2) DEFAULT NULL, > `conversions` int(11) DEFAULT NULL, > `goal_value` float(10,2) DEFAULT NULL, > KEY `idx_bounced_visits` (`site_id`,`index_date`), > KEY `idx_visits_month` (`site_id`,`keyword`,`index_month`,`index_year`) > ) ENGINE=MyISAM DEFAULT CHARSET=utf8 > > Was changed into: > > CREATE TABLE `ga_monthly_keyword_visits` ( > `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, > `site_id` int(11) DEFAULT NULL, > `index_date` int(11) DEFAULT NULL, > `index_month` int(11) NOT NULL, > `index_year` int(11) NOT NULL, > `keyword` varchar(128) DEFAULT NULL, > `source` varchar(30) DEFAULT NULL, > `visits` int(11) DEFAULT NULL, > `bounced_visits` int(11) DEFAULT NULL, > `transactions` int(11) DEFAULT NULL, > `revenue` float(10,2) DEFAULT NULL, > `value_per_click` float(10,2) DEFAULT NULL, > `conversions` int(11) DEFAULT NULL, > `goal_value` float(10,2) DEFAULT NULL, > PRIMARY KEY (`id`) > KEY `idx_bounced_visits` (`site_id`,`index_date`), > KEY `idx_visits_month` (`site_id`,`keyword`,`index_month`,`index_year`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 > > I tried Converting like this: > > CREATE TABLE new LIKE old; > ALTER TABLE new DROP (all indexes), ADD PRIMARY KEY (either autoincrement > or a 'natural' compound PK), ENGINE=InnoDB; > INSERT INTO new SELECT site_id, ..., goal_value FROM old; > ALTER TABLE new ADD INDEX (...); > > With only difference The original MyISAM table crashed and I took it from > backup, loading by LOAD DATA INFILE. > The problem, yes It loaded much quicker into Database 4H 16M to be precise, > but ALTER TABLE new ADD INDEX (...); > Put server into meditation mode. High I/O Wait rendered box unusable. I had > to interrupt the ALTER ADD KEY process after 5H of struggle. > > Now importing with Keys in place. It takes longer, much longer but at least > the server is working and customers do not complaint. > Schema design is awful, agree. I try to understand the process so will > redesign it soon, but any suggestions are welcome. > I' not a MySQL super guru so will be glad for hear your sorts, guys. > Thanks > > > On 27/06/13 00:04, Rick James wrote: >>> (`site_id`,`index_date`,`index_month`,`index_year`,`keyword`,`source`,`visits`, >>> >>> `bounced_visits`,`transactions`,`revenue`,`value_per_click`,`conversions`,`goal_value`); >> May we see the SHOW CREATE TABLE? Some of this smells bad. >> * It is almost always bad to split day/month/year into multiple fields. >> * Often a "fact" table, which this sounds like, should not have extra >> indexes. >> * Is each datatype as small as is practical? >> * Are any of the fields VARCHAR, and could be 'normalized'? >> >> I would expect this to the fastest way to convert (assuming you have the >> disk space): >> CREATE TABLE new LIKE old; >> ALTER TABLE new DROP (all indexes), ADD PRIMARY KEY (either autoincrement >> or a 'natural' compound PK), ENGINE=InnoDB; >> INSERT INTO new SELECT site_id, ..., goal_value FROM old; >> ALTER TABLE new ADD INDEX (...); >> >> What version of MySQL are you running? Newer versions do the ALTER TABLE >> faster (online??), and may require you to do one add at a time. >> >> Another issue... >> If the data in `old` is in the same order as the PRIMARY KEY of `new`, then >> INSERT..SELECT will run fast. (No need to jump around to find where to put >> each row.) >> Case 1: You are adding an AUTO_INC -- it will be in the 'right' order. >> Case 2: The new PK is approximately the order of the insertions into `old` >> -- probably run fast. (However, I do not see a likely natural PK that would >> allow this >> >> INSERT ... SELECT...ORDER BY (new PK) -- This would make the INSERT part >> fast, but the SELECT part would be slow. ("You can't win") >> >> Your task is all about disk hits. By understanding what MySQL has to do, >> you can 'predict' whether a plan will be "slow" or "slower". >> >> Back to the secondary indexes... >> What are the SELECTs that will benefit from them? (Sometimes discussing >> this can lead to fewer/better INDEXes. Often it leads to suggesting Summary >> Table(s).) >> >> >>> -----Original Message----- >>> From: Johan De Meersman [mailto:vegiv...@tuxera.be] >>> Sent: Wednesday, June 26, 2013 11:46 AM >>> To: li...@netrogenic.com; Jay Ess; mysql@lists.mysql.com >>> Subject: Re: space gone after MyISAM REPAIR TABLE >>> >>> You can't actually move innodb tables around until 5.6 where you have >>> transpotable tablespaces. >>> >>> I suggest having a good hard look at pt-online-schema-change or >>> whatsitcalled. >>> >>> Jay Ess <li...@netrogenic.com> wrote: >>>> On 2013-06-26 18:31, nixofortune wrote: >>>>> What would be the best way to convert BIG MyISAM table into InnoDB? >>>> We do not >>>>> have SLAVE. >>>> I would do it on another computer. Then copy the table to the server >>>> and then add the data that has been added from the original table. >>>> >>>> And/or i would experiment with TokuDB. I havent had the time to do it >>>> myself but will probably soon. I am too looking for a lengthy 1 >>>> billion+ row conversion. >>> -- >>> Sent from Kaiten Mail. Please excuse my brevity. >>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe: http://lists.mysql.com/mysql > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql