Convert yo innodb

[ASTRAPI]

Hello

How can i convert 2 tables that i want to innodb ?

e.x:

my_table1
my_table2


And then how can i convert them back from innodb to myisam if i am not happy with that change after a few days/weeks?

Thank you

[.Nuno.]

Maybe these can help:

http://community.invisionpower.com/topic/324564-easiest-way-to-convert-from-myisam-to-innodb/
http://community.invisionpower.com/topic/300439-fastest-way-to-convert-large-ipb-tables-over-to-innodb/

[ASTRAPI]

Ok thanks :)

I got the two commands:

ALTER TABLE tbl_name ENGINE = InnoDB;

to go back

ALTER TABLE tbl_name ENGINE = MyISAM;


But a few users report that i must drop some indexes that i didn't understand and also they report some errors for a full text scan related :(

I am using sphinx.

Can anyone please give more details?

ALTER TABLE ibf_posts DROP index `post`

What this will do ?

I want to convert the 2 bigger tables:

ibf_posts
ibf_topic_markers


Thank you

[.Nuno.]

Yes, you must drop the table index before convert to innodb:

http://community.invisionpower.com/topic/300439-fastest-way-to-convert-large-ipb-tables-over-to-innodb/page__view__findpost__p__2017956

[ASTRAPI]

Why?

I will have any problems with this?

OK I solved this one, make sure you run a backup.

in mysql> ALTER TABLE ibf_posts DROP index `post`

Then you can run the innodb command

for topics

ALTER TABLE ibf_topics DROP index `title`

It took 22 minutes to drop Full Text index on the topics table, so be patient (and run it in screen)

Why i have to do this and what is doing in a better explanation?

Thank you

[.Nuno.]

You must drop the index from your table before you convert to innodb
Mysql Full search uses indexes from Myisam, since you are changing to innodb you can not use fullsearch, instead you must use sphinx
The errors those users were getting what because of this.

ALTER TABLE ibf_posts DROP index `post` -> will drop posts table index named post
I don't have any topic_markers ... so you must look for the FULLTEXT type index name and do the same.

To go back just create the indexes and convert the table to MyISAM: ALTER TABLE ibf_posts ADD FULLTEXT (post)

[ASTRAPI]

Ok thanks :)

What i must also add to my.cnf for start optimizing innodb?

Is any cache or any other values that i must add there and then adjust them according to my server?

Thank you

[ASTRAPI]

Ok who knows how to find the index and do that drop and help me to convert two tables ?

I already use sphinx.

Thank you

[Volvospeed]

Why are you trying to convert to innodb? Have you made a complete backup already? Based on the level of some of these questions, it may be safer for you to leave well enough alone unless you have a reason to change the engine.

[ASTRAPI]

I am getting locked statements and is not from a mod as i can see from the sql query is just trying to update with a simple post my huge ibf_bost table and i think innodb will help or at least i can try.

[ASTRAPI]

Ok i am ready to try the Innodb.

But if i want to go back to Myisam do i have to create first the indexes and then convert or first convert and then create the indexes?



Are those commands correct?

ALTER TABLE ibf_posts ENGINE = MyISAM;

and

ALTER TABLE ibf_posts ADD FULLTEXT (post) <- I think that the () will not work.

Can anyone please give the correct syntax and order to run the commands On mysql 5.1.55?

Thank you

[cargelock]

Convert back to myisam then create indexes

create fulltext index index_name on table_name (field_name)

[ASTRAPI]

I am confused a bit :(

ALTER TABLE ibf_posts ADD FULLTEXT (post) <- I think that the () will not work.

or

create fulltext index index_name on table_name (field_name)


I need the correct syntax please.

[cargelock]

Either will suffice:
ALTER TABLE table_name ADD FULLTEXT (field_name)

or

CREATE FULLTEXT index index_name ON table_name (field_name)

Both work, that's the bizarre nature of (my)sql

[ASTRAPI]

Ok so if i use this:

ALTER TABLE ibf_posts ADD FULLTEXT post;

I will get back the indexes that i had named post and fulltext ?

I am confused as it suppose that table had some info inside and this command seems to me to just create the table without that info that i had inside or it will recreate that info?

[cargelock]

Both of those commands will return your fulltext indexes to how they were before you removed them.

FULLTEXT is an index method, so using either command will return you to a fulltext index on your posts table with the index name being post.

Neither command recreates tables as you don't need to as you're not dropping (deleting) any, you're just changing the indexes.

you can delete and add indexes as many times as you want.

[ASTRAPI]

Ok thanks :)