SMF 1.1, MyISAM and InnoDB

Started by Keeper, December 03, 2006, 10:15:26 AM

Previous topic - Next topic


SMF Version: SMF 1.1
The MyISAM/InnoDB guide posted here is for SMF 1.0. I tried to adapt the guide for 1.1, but I'd like to ask if these are correct.

smf_calendar MyISAM
smf_calendar_holidays MyISAM
smf_categories MyISAM
smf_collapsed_categories InnoDB
smf_log_actions InnoDB
smf_log_activity InnoDB
smf_log_banned InnoDB
smf_log_boards InnoDB
smf_log_errors InnoDB
smf_log_floodcontrol HEAP
smf_log_karma InnoDB
smf_log_mark_read InnoDB
smf_log_notify InnoDB
smf_log_online InnoDB
smf_log_polls InnoDB
smf_log_search_messages InnoDB
smf_log_search_results InnoDB
smf_log_search_subjects InnoDB
smf_log_search_topics InnoDB
smf_log_search_words InnoDB
smf_log_topics InnoDB
smf_membergroups MyISAM
smf_members InnoDB
smf_messages MyISAM
smf_message_icons MyISAM
smf_moderators MyISAM
smf_package_servers MyISAM
smf_permissions MyISAM
smf_personal_messages MyISAM
smf_pm_recipients InnoDB
smf_polls MyISAM
smf_poll_choices MyISAM
smf_postmoderation MyISAM
smf_sessions InnoDB
smf_settings InnoDB
smf_smileys MyISAM
smf_themes MyISAM
smf_topics InnoDB
smf_vwarnings MyISAM

Search index is large custom index. I made some searches about this but none of these really answered to my question if these are ok for SMF 1.1 or not. Thanks in advance.

Edit: My smf_messages is over 400MB and smf_personal_messages over 11MB in size, would it make any difference to change those to InnoDB? The original InnoDB guide did not include these tables at the very least.


They should be fine. I'd leave the messages & PM table there isn't really a benefit to changing those but you will loose the ability to use fulltext search on them.
Liverpool FC Forum with 14 million+ posts.



Can you or anyone else confirm if smf_log_search_words really benefits from being InnoDB as opposed to MyISAM? I created a new search index, stopped it after the first indexing batch, then converted it to InnoDB. SMF 1.1 updates smf_log_search_words ~50% slower when it is InnoDB.

It took 9 hours for InnoDB custom search index to reach around 20% completion as opposed to 4 hours when it is MyISAM. Is there a particular reason for this?

Edit: I'd like to know if I can finish the custom search index as MyISAM, then someday at good opportunity to switch to fulltext search index, download smf_log_search_words table to my linux box, convert the table to InnoDB, upload the table back again and switch to custom index without missing anything?

In other words, does SMF automatically update the custom search index to include posts that were posted between disabling and enabling custom search index?

Stüldt Håjt

What tables are converted here to InnoDB? And Ben what tables have you converted?


Mine are as follows

yabbse_attachments InnoDB
yabbse_ban_groups MyISAM
yabbse_ban_items MyISAM
yabbse_boards MyISAM
yabbse_board_permissions MyISAM
yabbse_calendar MyISAM
yabbse_calendar_holiday MyISAM
yabbse_calendar_holidays MyISAM
yabbse_categories MyISAM
yabbse_collapsed_categories InnoDB
yabbse_feedback MyISAM
yabbse_log_actions InnoDB
yabbse_log_activity InnoDB
yabbse_log_banned InnoDB
yabbse_log_boards InnoDB
yabbse_log_errors InnoDB
yabbse_log_floodcontrol HEAP
yabbse_log_karma InnoDB
yabbse_log_mark_read InnoDB
yabbse_log_notify InnoDB
yabbse_log_online MyISAM
yabbse_log_polls MyISAM
yabbse_log_search_messages MyISAM
yabbse_log_search_results InnoDB
yabbse_log_search_subjects InnoDB
yabbse_log_search_topics MyISAM
yabbse_log_topics InnoDB
yabbse_membergroups MyISAM
yabbse_members InnoDB
yabbse_messages MyISAM
yabbse_message_icons MyISAM
yabbse_moderators MyISAM
yabbse_package_servers MyISAM
yabbse_permissions MyISAM
yabbse_personal_messages MyISAM
yabbse_pm_recipients InnoDB
yabbse_polls MyISAM
yabbse_poll_choices MyISAM
yabbse_rssfeeds MyISAM
yabbse_sessions InnoDB
yabbse_settings InnoDB
yabbse_smileys MyISAM
yabbse_themes MyISAM
yabbse_topics InnoDB

Liverpool FC Forum with 14 million+ posts.