List:General Discussion« Previous MessageNext Message »
From:Rick James Date:June 26 2013 11:04pm
Subject:RE: space gone after MyISAM REPAIR TABLE
View as plain text  
> (`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:vegivamp@stripped]
> Sent: Wednesday, June 26, 2013 11:46 AM
> To: lists@stripped; 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 <lists@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

Thread
space gone after MyISAM REPAIR TABLEnixofortune24 Jun
  • Re: space gone after MyISAM REPAIR TABLEJohan De Meersman24 Jun
    • Re: space gone after MyISAM REPAIR TABLEReindl Harald24 Jun
      • Re: space gone after MyISAM REPAIR TABLEJohan De Meersman24 Jun
      • Re: space gone after MyISAM REPAIR TABLEnixofortune24 Jun
        • RE: space gone after MyISAM REPAIR TABLERick James25 Jun
          • Re: space gone after MyISAM REPAIR TABLEnixofortune26 Jun
            • Re: space gone after MyISAM REPAIR TABLEJay Ess26 Jun
              • Re: space gone after MyISAM REPAIR TABLEJohan De Meersman26 Jun
                • RE: space gone after MyISAM REPAIR TABLERick James26 Jun
                  • Re: space gone after MyISAM REPAIR TABLEnixofortune26 Jun
                    • Re: space gone after MyISAM REPAIR TABLEJay Ess27 Jun
                    • Re: space gone after MyISAM REPAIR TABLEDivesh Kamra27 Jun
            • Re: space gone after MyISAM REPAIR TABLEhsv26 Jun