MyBB Community Forums

MyBB 1.1.5, MySQL 4.1.20

MyBB database tables are often very little indexed (only primary key or even without primary...). Looking into "explain select" queries I see some optimizations which can be done:

ALTER TABLE `mybb_reputation` ADD PRIMARY KEY ( `uid` , `pid` , `adduid` ) 
ALTER TABLE `mybb_posts` ADD INDEX ( `tid` ) 
ALTER TABLE `mybb_templates` ADD INDEX ( `title` ) 
ALTER TABLE `mybb_attachments` ADD INDEX ( `pid` )
ALTER TABLE `mybb_privatemessages` ADD INDEX ( `uid` )
ALTER TABLE `mybb_threads` ADD INDEX ( `fid` )
ALTER TABLE `mybb_users` ADD INDEX ( `birthday` ) 

Creating those indexes gave sometimes 50% or more query-time decrease (in "mybb_users.birthday", "mybb_threads.fid", "mybb_privatemessages.uid", "mybb_posts.tid", "mybb_templates.title"). They should not produce any performance decrease in inserting/updating data because they corresponds to tables not frequently changed (except the table mybb_posts).

Used on forum with ~3000 users (daily ~30 new users), ~3000 threads (daily ~30 new), ~30000 posts (daily ~300 new) - numbers are rounded, so all look like 3 .