MyISAM vs InnoDB for MyBB?

[Eldenroot]

Hi guys,

as title says - I have found many topics about this issue here on MyBB.com, but none of them contains any information when to use InnoDB and when to use MyISAM.

It would be nice to heard your feedback and experiences. Are there any lets say recommends?

For example:
when your forum has over 1kk posts -> use InnoDB etc?

Also an article in MyBB docs would be nice

Thank you all for your reply.

[Nathan Malcolm]

See: https://en.wikipedia.org/wiki/Comparison...se_engines

tl;dr Use InnoDB. MyBB 2.0 will be using it by default too.

[dragonexpert]

I responded to your PM about this.

[Eldenroot]

@Nathan Malcolm - thank you, I will migrate to InnoDB

@dragonexpert - thank you, I will sent you a reply as soon as possible.

I will try to optimize my forum, loading times are not good

---

So I followed this tutorial:
1) I added new DB name and credentials into my MyBB config file 
2) I run this php file

<?php

define("IN_MYBB", 1);
define("NO_ONLINE", 1);
define("NO_PLUGINS", 1);
require_once "global.php";
if(!$mybb->usergroup['cancp'])
{
error_no_permission();
}
$tablelist = $db->list_tables($config['database']['database'], TABLE_PREFIX);
foreach($tablelist as $table)
{
$db->write_query("ALTER TABLE "  . $table . " ENGINE=Innodb");
echo "Converted table $table to Innodb.<br />";
}
echo "Table conversion complete.<br />";
?>

3) I run these SQLs in phpMyAdmin

ALTER TABLE mybb_threads DROP INDEX subject;
ALTER TABLE mybb_posts DROP INDEX message;

4) I run these SQLs in phpMyAdmin

ALTER TABLE mybb_posts ENGINE=Innodb;
ALTER TABLE mybb_threads ENGINE=Innodb; 

Now I have few questions:
- What else, all tables are now migrated to InnoDB (is there any point to use MyISAM for some of them?)
- Can I use "similar threads" on InnoDB (the table shown below the thread)
- Is there anything how can I optimize my DB settings?

Thank you very much!

[laie_techie]

MyISAM has always supported FULLTEXT search, but lacks per-row locking. Additionally, MyISAM lacks support for transactions and foreign keys.
InnoDB supports transactions and foreign keys. It has supported FULLTEXT since MyQL 5.6.4 (MariaDB 10.0).

Which engine should be used for which table depends on the table's needs and your version of MySQL. If you have MySQL 5.6.4 or higher, use InnoDB for most tables. One possible exception is the session table can be sped up with the MEMORY engine if you have enough RAM.

You should use MyISAM for tables that need FULLTEXT (such as posts) and you are using an older version of MySQL; InnoDB for everything else.

[Eldenroot]

I have MySQL 5.6.28 - I converted everything to InnoDB.

So do you recommend to convert posts table into MyISAM?

EDIT:
Added debug info for index and show thread
https://dl.dropboxusercontent.com/u/2854...0page.docx

https://dl.dropboxusercontent.com/u/2854...hread.docx

[laie_techie]

I have MySQL 5.6.28 - I converted everything to InnoDB.

So do you recommend to convert posts table into MyISAM?

InnoDB in MySQL 5.6.28 does support FULLTEXT indexes. You should probably add back the subject and messages indexes to aid searching.

[Eldenroot]

Yeah, I run this (check point 4)

ALTER TABLE mybb_posts ENGINE=Innodb;
ALTER TABLE mybb_threads ENGINE=Innodb; 

[laie_techie]

Restore the indexes you deleted. Since you are running a recent version of MySQL, you can have fulltext on InnoDB tables:

create fulltext index message on mybb_posts(message);
create fulltext index subject on mybb_threads(subject);

[Eldenroot]

@laie_techie - I got this error

#1061 - Duplicate key name 'message'

One more thing - my DB size went from 20 MB to 37 MB after migration from MyISAM to InnoDB, is it normal? Thank you