TechnocratTechnocrat’s Random Thoughts

Over the last few days I have been expanding my knowledge on MySQL indexes along with other useful tidbits.  I am not super strong on SQL.  I know enough to get by but it’s an area I would like to know more about.

Anyways after reading some good articles I began to find areas where we can improve both phpBB-Evolution and Nuke-Evolution.  One of which is the lack of proper indexing.  Nuke is a villain when it comes to this.  The indexing in its piss poor to put it nicely.  So I figured it would be a good place to implement some of the ideas I have found.  No better place to do it than the NE site….right!?

So I started going through and making better indexing.  I started with the core files and I just got done.  I have to say the difference is extremely noticeable.  I can already tell the page is loading much faster in many areas.  I also turned off the optimizing so I can see what tables get overhead and I can use that as a key as to what needs fixing.  So we shall see what the effects are long term but so far it has been positive.

If you are interested I have been keeping note on what I have done.  So give it a try if you want.

[UPDATE] So I went too far with some indexing.  Though it sped up the site it took up to much memory and caused SQL to crash   so I had to reboot the server and the site was down for a day.  So I took out the biggest indexes and I am leaving just the ones that I think will help and not cause memory issues.  Enjoy.

ALTER TABLE `nuke_modules` ADD INDEX ( `active` );
ALTER TABLE `nuke_blocks` ADD INDEX `active_weight` ( `active` , `weight` )  ;
ALTER TABLE `nuke_blocks` ADD INDEX `time_bid` ( `time` , `bid` );
ALTER TABLE `nuke_stories` ADD INDEX `topic_ihome_time` ( `topic` , `ihome` , `time` );
ALTER TABLE `nuke_stories` ADD INDEX `topic_sid` ( `topic` , `sid` );
ALTER TABLE `nuke_banner` ADD INDEX `position_active` ( `position` , `active` );
ALTER TABLE `nuke_session` ADD INDEX ( `uname` );
ALTER TABLE `nuke_evolution` ADD INDEX ( `evo_field` );
ALTER TABLE `nuke_bbadvanced_username_color` ADD INDEX `groupid_groupweight` ( `group_id` , `group_weight` );
ALTER TABLE `nuke_bbranks` ADD INDEX `rankspecial_rankmin` ( `rank_special` , `rank_min` );
ALTER TABLE `nuke_bbcategories` ADD INDEX ( `cat_title` );
ALTER TABLE `nuke_themes` ADD INDEX ( `theme_name` );
ALTER TABLE `nuke_nsnst_admins` ADD INDEX ( `password_md5` );
ALTER TABLE `nuke_session` ADD INDEX `guest_uname` ( `guest` , `uname` );
ALTER TABLE `nuke_session` ADD INDEX ( `host_addr` );
ALTER TABLE `nuke_bbadvanced_username_color` ADD INDEX ( `group_name` );