MySQL: MyISAM or INNODB?

[Shamil]

Recently, vBulletin suggested that we convert our tables and engines to INNODB, slating many performance improvements, such as: faster query times and fewer table-locking issues.

Now, INNODB doesn't support FULLTEXT searching, which means that an external searching mechanism needs to be used.

What do you recommend and what schema will XF be using?

 

[Ghan_04]

The installer setups up everything correctly. Some tables have to use MyISAM. So it will set them as such. Others require MEMORY, and it sets them that way as well.

 

[Mario Estrada]

I've created a SQL script that alters only the required tables to InnoDB from MyISAM. See attached.

Basically it does something like this:

SET FOREIGN_KEY_CHECKS = 0;
 
ALTER TABLE archived_import_log ENGINE=InnoDB;
ALTER TABLE xf_addon ENGINE=InnoDB;
...
ALTER TABLE xf_warning_definition ENGINE=InnoDB;
 
SET FOREIGN_KEY_CHECKS = 1;

I'm not sure if disabling foreign key checks is necessary but it might prevent issues when switching. Any thoughts?

PS: You might not have the archived_import_log in your database and it shouldn't matter what engine it uses. You can safely remove that line.

 

[Bill.D]

Hey All,

I have been slowly testing the transition of my tables over to InnoDB. When I do a "SHOW TABLE STATUS" I have noted that the Row_format hase changed from Dynamic to Compact and the Max_data_length is changed to 0 from its previous very large number. Is this ok? Do I need to change them back?

Thanks,
-Bill

 

[DJ XtAzY]

The only tables which are MyISAM are:

xf_import_log
xf_search_index
xf_session
xf_session_admin

The rest are InnoDB apart from 3 which are MEMORY:

xf_attachment_view
xf_session_activity
xf_thread_view

Sorry for bring up an old thread. Is this still the case?

 

[Jeremy]

Without checking, I believe so.