Advertisement:

Author Topic: SMF 1.1: varchar and index performance tweaks  (Read 11119 times)

Offline Keeper

  • Semi-Newbie
  • *
  • Posts: 74
SMF 1.1: varchar and index performance tweaks
« 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. :)

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: SMF 1.1: varchar and index performance tweaks
« Reply #1 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 :)

Offline Keeper

  • Semi-Newbie
  • *
  • Posts: 74
Re: SMF 1.1: varchar and index performance tweaks
« Reply #2 on: September 13, 2009, 03:11:44 AM »
Those queries are for SMF 2.0.

Offline Keeper

  • Semi-Newbie
  • *
  • Posts: 74
Re: SMF 1.1: varchar and index performance tweaks
« Reply #3 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;

Offline Something like that

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,496
  • Gender: Male
  • ]
Re: SMF 1.1: varchar and index performance tweaks
« Reply #4 on: September 13, 2009, 12:39:21 PM »
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;


Offline wnff_chief

  • Semi-Newbie
  • *
  • Posts: 61
Re: SMF 1.1: varchar and index performance tweaks
« Reply #5 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?

Offline greyknight17

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 14,890
  • Gender: Male
    • Kevin's Resource Center
Re: SMF 1.1: varchar and index performance tweaks
« Reply #6 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;