[Performance] Upgrading Tables To Innodb
#1
This script will convert all your tables to the much better Innodb storage engine than MyISAM.  This is very helpful for larger forums with many users online.  If you do not have MySQL version 5.6 or higher you have three choices to make: 
1) Upgrade to MySQL 5.6 or higher ( you may need to contact your host for this. )
2) Keep Full Text Index searches and let the mybb_posts and mybb_threads tables stay as MyISAM
3) Drop the index on message for the mybb_posts table and the index subject on the mybb_threads table.

<?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 />";
?>

Once the file is executed, you should delete it off your server.
Reply
#2
hi,good tutorials,i tried but how drop post and threads index?

What code is code I run from phpMyAdmin for drop post and threads index ?

thanks.
_______________________________________________

It is not disgraceful to ask, it is disgraceful no to ask.


sory for my english .
Reply
#3
ALTER TABLE mybb_threads DROP INDEX subject;
ALTER TABLE mybb_posts DROP INDEX message;
Reply
#4
Thanks dragonexpert,

Table conversion complete.

First after conversion:

thread and post error

1214 - The used table type doesn't support FULLTEXT indexes

How fix this error?

2:How change this?

http://prntscr.com/5pjtrj

3:and ı tried repair and optimize tables,mesage,

http://prntscr.com/5pjvak


The storage engine for the table doesn't support r...

how fix this?

thanks.
_______________________________________________

It is not disgraceful to ask, it is disgraceful no to ask.


sory for my english .
Reply
#5
Quote:1214 - The used table type doesn't support FULLTEXT indexes

How fix this error?
You need to drop the indexes with the queries I posted above, prior to running the script. After doing that you can run these two queries:
ALTER TABLE mybb_posts ENGINE=Innodb;
ALTER TABLE mybb_threads ENGINE=Innodb;

Quote:2:How change this?

http://prntscr.com/5pjtrj

If you are referring to the default table type, I'm not sure how to change that, your host may know though.

Quote:3:and ı tried repair and optimize tables,mesage,

http://prntscr.com/5pjvak


The storage engine for the table doesn't support r...

how fix this?
You can't run the optimize command for Innodb tables. If there is significant overhead and you want to remove it, change the storage type to a different one then change it back because when you change the storage engine it rebuilds the indices. Note that with larger tables it may take more time to execute.
Reply
#6
thanks dragonexpert.
_______________________________________________

It is not disgraceful to ask, it is disgraceful no to ask.


sory for my english .
Reply
#7
Quote:ALTER TABLE mybb_threads DROP INDEX subject;
ALTER TABLE mybb_posts DROP INDEX message;

For readding the index to your table

ALTER TABLE mybb_threads ADD FULLTEXT ( subject )
ALTER TABLE mybb_posts ADD FULLTEXT ( message )
Reply
#8
Great script, gonna use it but how do i install this or execute it?
Happy New Year!
Reply
#9
(2015-01-06, 01:59 PM)dragonexpert Wrote: This script will convert all your tables to the much better Innodb storage engine than MyISAM.  This is very helpful for larger forums with many users online.  If you do not have MySQL version 5.6 or higher you have three choices to make: 
1) Upgrade to MySQL 5.6 or higher ( you may need to contact your host for this. )
2) Keep Full Text Index searches and let the mybb_posts and mybb_threads tables stay as MyISAM
3) Drop the index on message for the mybb_posts table and the index subject on the mybb_threads table.

<?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 />";
?>

Once the file is executed, you should delete it off your server.

Important!

For those that choose to Drop the index on message for the mybb_posts table and the index subject on the mybb_threads table. Ensure to disable Similar threads in Admin CP
Hovatek Forum | Hovatek Blog |

Hovatek....just a button away!
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)