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.
I responded to your PM about this.
@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!
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.
(2016-12-12, 06:50 PM)Eldenroot Wrote: [ -> ]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.
Yeah, I run this (check point 4)
ALTER TABLE mybb_posts ENGINE=Innodb;
ALTER TABLE mybb_threads ENGINE=Innodb;
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);
@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