Text only | Text with Images

Simple Machines Community Forum

SMF Support => Server Performance and Configuration => Topic started by: Keeper on September 13, 2009, 03:07:56 AM

Title: SMF 1.1: varchar and index performance tweaks
Post by: Keeper on September 13, 2009, 03:07:56 AM
I've seen a couple of topics discussing about converting tinytext/blob columns to varchar and adding indexes/keys to some tables. Unfortunately I couldn't find sql queries for SMF 1.1 anywhere to apply these tweaks. I see most of those if not all will be in the next RC of SMF 2.0, but I'd like to get those tweaks early for SMF 1.1.

Thanks. :)
Title: Re: SMF 1.1: varchar and index performance tweaks
Post by: Something like that on September 13, 2009, 03:10:56 AM
See the first post in this thread: http://www.simplemachines.org/community/index.php?topic=293441.0 and look for "Additional Item #1: Get rid of Text tables."

Edit: I see they're only listed for 2.0. Use a tool like phpMyAdmin and do the conversion with it instead :)
Title: Re: SMF 1.1: varchar and index performance tweaks
Post by: Keeper on September 13, 2009, 03:11:44 AM
Those queries are for SMF 2.0.
Title: Re: SMF 1.1: varchar and index performance tweaks
Post by: Keeper on September 13, 2009, 05:25:44 AM
I set up a copy of my forums to try out varchar conversion based on the SMF 2.0 queries. Went through each table to check out tinytexts and compare them to the queries in above link. Queries for SMF 1.1 are listed below for any other lazy ass like me. :)

I'm still wondering about those additional indexes/keys though.

Code Select

ALTER TABLE `smf_attachments` CHANGE `filename` `filename` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_groups` CHANGE `reason` `reason` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_items` CHANGE `hostname` `hostname` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_items` CHANGE `email_address` `email_address` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_boards` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_categories` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_banned` CHANGE `email` `email` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_membergroups` CHANGE `stars` `stars` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_package_servers` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_package_servers` CHANGE `url` `url` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_polls` CHANGE `question` `question` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_polls` CHANGE `posterName` `posterName` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_poll_choices` CHANGE `label` `label` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_settings` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_themes` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_members` CHANGE `lngfile` `lngfile` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `realName` `realName` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `emailAddress` `emailAddress` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `personalText` `personalText` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `websiteTitle` `websiteTitle` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `websiteUrl` `websiteUrl` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `location` `location` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `icq` `icq` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `msn` `msn` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `avatar` `avatar` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `usertitle` `usertitle` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `memberIP` `memberIP` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `secretQuestion` `secretQuestion` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `additionalGroups` `additionalGroups` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `memberIP2` `memberIP2` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_messages` CHANGE `posterName` `posterName` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_messages` CHANGE `posterEmail` `posterEmail` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_messages` CHANGE `posterIP` `posterIP` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_messages` CHANGE `modifiedName` `modifiedName` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_personal_messages` CHANGE `fromName` `fromName` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_personal_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL;
Title: Re: SMF 1.1: varchar and index performance tweaks
Post by: Something like that on September 13, 2009, 12:39:21 PM
Quote from: Keeper on September 13, 2009, 05:25:44 AM
I set up a copy of my forums to try out varchar conversion based on the SMF 2.0 queries. Went through each table to check out tinytexts and compare them to the queries in above link. Queries for SMF 1.1 are listed below for any other lazy ass like me. :)

I'm still wondering about those additional indexes/keys though.

Thanks for listing out all the changes :)

I've merge some of them together, so it runs quicker for anyone else:
Code Select

ALTER TABLE `smf_attachments` CHANGE `filename` `filename` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_groups` CHANGE `reason` `reason` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_items` CHANGE `hostname` `hostname` VARCHAR(255) NOT NULL, CHANGE `email_address` `email_address` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_boards` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_categories` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_banned` CHANGE `email` `email` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_membergroups` CHANGE `stars` `stars` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_package_servers` CHANGE `name` `name` VARCHAR(255) NOT NULL, CHANGE `url` `url` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_polls` CHANGE `question` `question` VARCHAR(255) NOT NULL, CHANGE `posterName` `posterName` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_poll_choices` CHANGE `label` `label` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_settings` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_themes` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_members` CHANGE `lngfile` `lngfile` VARCHAR(255) NOT NULL, CHANGE `realName` `realName` VARCHAR(255) NOT NULL, CHANGE `emailAddress` `emailAddress` VARCHAR(255) NOT NULL, CHANGE `personalText` `personalText` VARCHAR(255) NOT NULL, CHANGE `websiteTitle` `websiteTitle` VARCHAR(255) NOT NULL, CHANGE `websiteUrl` `websiteUrl` VARCHAR(255) NOT NULL, CHANGE `location` `location` VARCHAR(255) NOT NULL, CHANGE `icq` `icq` VARCHAR(255) NOT NULL, CHANGE `msn` `msn` VARCHAR(255) NOT NULL, CHANGE `avatar` `avatar` VARCHAR(255) NOT NULL, CHANGE `usertitle` `usertitle` VARCHAR(255) NOT NULL, CHANGE `memberIP` `memberIP` VARCHAR(255) NOT NULL, CHANGE `secretQuestion` `secretQuestion` VARCHAR(255) NOT NULL, CHANGE `additionalGroups` `additionalGroups` VARCHAR(255) NOT NULL, CHANGE `memberIP2` `memberIP2` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL, CHANGE `posterName` `posterName` VARCHAR(255) NOT NULL, CHANGE `posterEmail` `posterEmail` VARCHAR(255) NOT NULL, CHANGE `posterIP` `posterIP` VARCHAR(255) NOT NULL, CHANGE `modifiedName` `modifiedName` VARCHAR(255) NOT NULL, CHANGE `fromName` `fromName` VARCHAR(255) NOT NULL, CHANGE `subject` `subject` VARCHAR(255) NOT NULL;


Title: Re: SMF 1.1: varchar and index performance tweaks
Post by: wnff_chief on December 18, 2009, 02:24:58 PM
I got errors for fromName and subject using your queries stating they did not exist (SMF 1.1.10) so I removed them and ran all the other queries successfully.

Is there any cause for concern about the two that errored out?
Title: Re: SMF 1.1: varchar and index performance tweaks
Post by: greyknight17 on February 18, 2010, 08:06:00 PM
Is this issue resolved yet? You are getting an error for those two fields because they are for a different table. Try the below instead:

Code Select
ALTER TABLE `smf_attachments` CHANGE `filename` `filename` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_groups` CHANGE `reason` `reason` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_items` CHANGE `hostname` `hostname` VARCHAR(255) NOT NULL, CHANGE `email_address` `email_address` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_boards` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_categories` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_banned` CHANGE `email` `email` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_membergroups` CHANGE `stars` `stars` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_package_servers` CHANGE `name` `name` VARCHAR(255) NOT NULL, CHANGE `url` `url` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_polls` CHANGE `question` `question` VARCHAR(255) NOT NULL, CHANGE `posterName` `posterName` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_poll_choices` CHANGE `label` `label` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_settings` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_themes` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `lngfile` `lngfile` VARCHAR(255) NOT NULL, CHANGE `realName` `realName` VARCHAR(255) NOT NULL, CHANGE `emailAddress` `emailAddress` VARCHAR(255) NOT NULL, CHANGE `personalText` `personalText` VARCHAR(255) NOT NULL, CHANGE `websiteTitle` `websiteTitle` VARCHAR(255) NOT NULL, CHANGE `websiteUrl` `websiteUrl` VARCHAR(255) NOT NULL, CHANGE `location` `location` VARCHAR(255) NOT NULL, CHANGE `icq` `icq` VARCHAR(255) NOT NULL, CHANGE `msn` `msn` VARCHAR(255) NOT NULL, CHANGE `avatar` `avatar` VARCHAR(255) NOT NULL, CHANGE `usertitle` `usertitle` VARCHAR(255) NOT NULL, CHANGE `memberIP` `memberIP` VARCHAR(255) NOT NULL, CHANGE `secretQuestion` `secretQuestion` VARCHAR(255) NOT NULL, CHANGE `additionalGroups` `additionalGroups` VARCHAR(255) NOT NULL, CHANGE `memberIP2` `memberIP2` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL, CHANGE `posterName` `posterName` VARCHAR(255) NOT NULL, CHANGE `posterEmail` `posterEmail` VARCHAR(255) NOT NULL, CHANGE `posterIP` `posterIP` VARCHAR(255) NOT NULL, CHANGE `modifiedName` `modifiedName` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_personal_messages` CHANGE `fromName` `fromName` VARCHAR(255) NOT NULL, CHANGE `subject` `subject` VARCHAR(255) NOT NULL;
Text only | Text with Images