> (`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
