Invision Community 4: SEO, prepare for v5 and dormant account notifications Matt November 11, 2024Nov 11
Posted March 11, 201113 yr 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
March 11, 201113 yr 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/
March 11, 201113 yr Author 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
March 11, 201113 yr 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
March 11, 201113 yr Author 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
March 12, 201113 yr 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 postI 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)
March 13, 201113 yr Author 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
March 27, 201113 yr Author 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
March 28, 201113 yr 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.
March 29, 201113 yr Author 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.
March 30, 201113 yr Author 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
March 31, 201113 yr Convert back to myisam then create indexes create fulltext index index_name on table_name (field_name)
March 31, 201113 yr Author 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.
April 1, 201113 yr 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
April 1, 201113 yr Author 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?
April 1, 201113 yr 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.
Archived
This topic is now archived and is closed to further replies.