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 <[email protected]> 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:[email protected]]
>>> Sent: Wednesday, June 26, 2013 11:46 AM
>>> To: [email protected]; Jay Ess; [email protected]
>>> 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 <[email protected]> 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
