mysql table optimisation suggestions. -long post-

neveriwas

Here is what I have so far for the mysql table layout. As you can see, my knowledge of mysql is still within entry level. I would like to see if there are other ways of putting the following database together so that it is less stressfull on the mysql server and faster to find the data needed. That way I can learn about new ways of doing things first hand with the project that I am working on. (just reading books doesn't cut it for me) Hopefully with what I have written down you can see what I am trying to get at with the entries. If not, just guess I suppose and I will probably be able to figure things out.

I apreciate your efforts in helping me to learn better coding durring this project. Hopefully i'll soon be able to help you guys out in return :-)

DROP TABLE IF EXISTS ruler1_players;
CREATE TABLE ruler1_players (
  idnum mediumint(5) unsigned NOT NULL auto_increment,
  username VARCHAR(25) NOT NULL,
  password VARCHAR(25) NOT NULL,
  email tinytext NOT NULL,
  IP tinytext NOT NULL,
  signedup int(11) NOT NULL default '0',
  ismulti tinyint(1) unsigned NOT NULL default '0',
  isnpc tinyint(1) unsigned NOT NULL default '0',
  isspy tinyint(1) unsigned NOT NULL default '0',
  isemperor tinyint(1) unsigned NOT NULL default '0',
  disabled tinyint(1) unsigned NOT NULL default '0',
  valcode tinytext NOT NULL,
  validated tinyint(1) unsigned NOT NULL default '0',
  online tinyint(1) unsigned NOT NULL default '0',
  vacation smallint(5) unsigned NOT NULL default '0',
  idle tinyint(1) unsigned NOT NULL default '0',
  last_turntime int(11) unsigned NOT NULL default '0',
  premiumacct tinyint(1) unsigned NOT NULL default '0', 
  clan_memid smallint(5) unsigned NOT NULL default '0',  

  rank mediumint(5) unsigned NOT NULL default '0',
  wins mediumint(7) unsigned NOT NULL default '0',
  turns smallint(5) unsigned NOT NULL default '0',
  turnsused mediumint(7) unsigned NOT NULL default '0',
  emperor_name tinytext NOT NULL,
  emperor_sex tinyint(1) unsigned NOT NULL default '0',
  emperor_age tinyint(3) unsigned NOT NULL,
  emperor_leadership tinyint(3) unsigned NOT NULL,
  emperor_judgment tinyint(3) unsigned NOT NULL,
  emperor_planning tinyint(3) unsigned NOT NULL,
  emperor_charm tinyint(3) unsigned NOT NULL,
  emperor_body tinyint(3) unsigned NOT NULL,
  emperor_battle tinyint(3) unsigned NOT NULL,
  ismarried tinyint(1) unsigned NOT NULL default '0',
  married_to mediumint(5) unsigned NOT NULL default '0',
  parent_ID mediumint(5) unsigned NOT NULL default '0',
  msgcred tinyint(2) unsigned NOT NULL default '0',
  msgtime int(11) unsigned NOT NULL default '0',
  newstime int(11) unsigned NOT NULL default '0',
  mail tinyint(1) unsigned NOT NULL default '0',
  clan_num smallint(5) unsigned NOT NULL,
PRIMARY KEY (idnum),
) TYPE=MyISAM;

DROP TABLE IF EXISTS `ruler1_titles`;
CREATE TABLE `ruler1_titles` (
  `id` int(11) NOT NULL default '0',
  `titles` text NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM COMMENT='table for titles';
INSERT INTO `ruler1_titles` (`id`, `titles`) VALUES (0, 'Normal User'),
(1, 'Special User'),
(2, 'Banned From Forum'),
(3, 'Banned From Game'),
(4, 'Forum Moderator'),
(5, 'Forum Admin'),
(6, 'Game Admin'),
(7, 'Owner');

DROP TABLE IF EXISTS ruler1_provinces;
CREATE TABLE ruler1_provinces (
  prov_id tinyint(3) unsigned NOT NULL auto_increment,
  prov_name tinyint(3) unsigned NOT NULL default '0',
  owner_id mediumint(5) unsigned NOT NULL default '0',
  store_rate tinyint(3) unsigned NOT NULL default '0',
  artisans tinyint(3) unsigned NOT NULL default '0',
  gold smallint(5) unsigned NOT NULL default '0',
  food_units smallint(5) unsigned NOT NULL default '0',
  farmers tinyint(3) unsigned NOT NULL default '0',
  troops smallint(5) unsigned NOT NULL default '0',
  morale tinyint(3) unsigned NOT NULL default '0',
  taxrate tinyint(3) unsigned NOT NULL default '30',
  troop_arms tinyint(3) unsigned NOT NULL default '0',
  troop_skill tinyint(3) unsigned NOT NULL default '0',
  town_economy tinyint(3) NOT NULL default '0',
  taxcollectors tinyint(3) NOT NULL default '0',
  castle_def tinyint(3) NOT NULL default '0',
  percent_calvary tinyint(3) unsigned NOT NULL,
  percent_infantry tinyint(3) unsigned NOT NULL,
  percent_archers tinyint(3) unsigned NOT NULL,
  percent_bombarder tinyint(3) unsigned NOT NULL,
  masonry tinyint(3) NOT NULL default '0',
PRIMARY KEY (prov_id)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `ruler1_troops`;
CREATE TABLE `ruler1_troops` (
  `id` int(11) unsigned NOT NULL default '0',
  `titles` text NOT NULL,
  `typeid` tinyint(2) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;
INSERT INTO `ruler1_troops` (`id`, `typeid`, `titles`) VALUES (0, 1, 'Calvary'),
(1, 2, 'Infantry'),
(2, 3, 'Archers'),
(3, 4, 'Bombarder');

DROP TABLE IF EXISTS ruler1_items;
CREATE TABLE ruler1_items (
  itemid tinyint(3) unsigned NOT NULL default '0',
  itemname tinytext NOT NULL,
  itembaseprice_buy tinyint(2) unsigned NOT NULL default '0',
  itembaseprice_sell tinyint(2) unsigned NOT NULL default '0',
  istroops BOOL NOT NULL default '0',
  iswool BOOL NOT NULL default '0',
  issilk BOOL NOT NULL default '0',
  iswine BOOL NOT NULL default '0',
  isglass BOOL NOT NULL default '0',
  isbaubles BOOL NOT NULL default '0',
  isrugs BOOL NOT NULL default '0',
  ismetalore BOOL NOT NULL default '0',
  isspice BOOL NOT NULL default '0',
  ismedicine BOOL NOT NULL default '0',
  isfurnature BOOL NOT NULL default '0',
  islumber BOOL NOT NULL default '0',
PRIMARY KEY (itemid)
) TYPE=MyISAM;

DROP TABLE IF EXISTS ruler1_stores;
CREATE TABLE ruler1_stores (
  storeid tinyint(2) unsigned NOT NULL default '0',
  merchantname tinytext NOT NULL,
  isavail tinyint(1) unsigned NOT NULL default '0',
  availin text NOT NULL,
  woolavail_buy BOOL NOT NULL default '0',
  silkavail_buy BOOL NOT NULL default '0',
  wineavail_buy BOOL NOT NULL default '0',
  glassavail_buy BOOL NOT NULL default '0',
  baublesavail_buy BOOL NOT NULL default '0',
  rugsavail_buy BOOL NOT NULL default '0',
  metaloreavail_buy BOOL NOT NULL default '0',
  spiceavail_buy BOOL NOT NULL default '0',
  medicineavail_buy BOOL NOT NULL default '0',
  furnatureavail_buy BOOL NOT NULL default '0',
  lumberavail_buy BOOL NOT NULL default '0',
  woolavail_sell BOOL NOT NULL default '0',
  silkavail_sell BOOL NOT NULL default '0',
  wineavail_sell BOOL NOT NULL default '0',
  glassavail_sell BOOL NOT NULL default '0',
  baublesavail_sell BOOL NOT NULL default '0',
  rugsavail_sell BOOL NOT NULL default '0',
  metaloreavail_sell BOOL NOT NULL default '0',
  spiceavail_sell BOOL NOT NULL default '0',
  medicineavail_sell BOOL NOT NULL default '0',
  furnatureavail_sell BOOL NOT NULL default '0',
  lumberavail_sell BOOL NOT NULL default '0',
PRIMARY KEY  (storeid)
) TYPE=MyISAM;

DROP TABLE IF EXISTS ruler1_clan;
CREATE TABLE ruler1_clan (
  num smallint(5) unsigned NOT NULL auto_increment,
  founder mediumint(8) unsigned NOT NULL default '0',
  asst mediumint(8) unsigned NOT NULL default '0',
  fa1 mediumint(8) unsigned NOT NULL default '0',
  fa2 mediumint(8) unsigned NOT NULL default '0',
  ally1 smallint(5) unsigned NOT NULL default '0',
  ally2 smallint(5) unsigned NOT NULL default '0',
  ally3 smallint(5) unsigned NOT NULL default '0',
  war1 smallint(5) unsigned NOT NULL default '0',
  war2 smallint(5) unsigned NOT NULL default '0',
  war3 smallint(5) unsigned NOT NULL default '0',
  pic tinytext NOT NULL,
  url tinytext NOT NULL,
  motd text NOT NULL,
  members smallint(6) NOT NULL default '1',
  name tinytext NOT NULL,
  tag tinytext NOT NULL,
  password tinytext NOT NULL,
PRIMARY KEY (num)
) TYPE=MyISAM;

DROP TABLE IF EXISTS ruler1_forum;
CREATE TABLE ruler1_forum (
  forum_id tinyint(4) unsigned NOT NULL auto_increment,
  forum_name varchar(40) NOT NULL default '',
  forum_owner mediumint(5) unsigned NOT NULL default '0',
  notes tinytext NOT NULL,
  mod tinyint(4) NOT NULL default '0',
PRIMARY KEY  (forum_id)
) TYPE=MyISAM;
INSERT INTO ruler1_forum (forum_id, forum_name, forum_owner, notes, mod) VALUES (6, 'General', 0, 'Talk about anything under the sky.', 0);
INSERT INTO ruler1_forum (forum_id, forum_name, forum_owner, notes, mod) VALUES (7, 'Alliance Talk', 0, 'Diplomacy of Alliances.', 0);
INSERT INTO ruler1_forum (forum_id, forum_name, forum_owner, notes, mod) VALUES (9, 'Bugs and Suggestions', 0, 'Have an idea about the game that you would like to be heard?', 0);

DROP TABLE IF EXISTS ruler1_thread;
CREATE TABLE ruler1_thread (
  thread_id smallint(4) unsigned NOT NULL auto_increment,
  parent_id smallint(4) unsigned NOT NULL default '0',
  forum_id smallint(4) unsigned NOT NULL default '0',
  title tinytext NOT NULL,
  content mediumtext NOT NULL,
  author tinytext NOT NULL,
  added datetime NOT NULL default '00-00-0000 00:00:00',
  modified datetime NOT NULL default '00-00-0000 00:00:00',
PRIMARY KEY  (thread_id)
) TYPE=MyISAM;

DROP TABLE IF EXISTS ruler1_mail;
CREATE TABLE ruler1_mail (
  id int(10) unsigned NOT NULL auto_increment,
  time int(11) NOT NULL default '0',
  origin mediumint(8) unsigned NOT NULL default '0',
  dest mediumint(8) unsigned NOT NULL default '0',
  msg text NOT NULL,
  replied tinyint(1) unsigned NOT NULL default '0',
  deleted tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (id),
KEY dest(dest),
KEY time(time),
KEY deleted(deleted)
) TYPE=MyISAM;

m_tt

The size of the table does not matter as long as you have good indexes. I've found indexes to be the most important part of any table design. Especially if you are dealing with large amounts of data. I have a table now that has 5.3million rows and 1.1GB in size that is constantly being hammered and my mysqld process runs at a stead 0.00% 🙂

neveriwas

Thats just it, what is considered a good index? I know just basic mysql right now. Enough to get by with. But not enough for performance. I am trying to learn about the optimisation and performance tricks on this particular project.

Thanks for the heads up too.

neveriwas

Even though, for me, reading information has a large learning curve. I have been trying to read up on mysql structure and indexing and such. However, I am not finding much that is pretty clear on it. My flaw, I suppose, is that I have had to learn how to do things first hand durring a project undertaking of my own.

What do you folks suggest I do at this point in time?

Thanks for all of your support!

trooper

Invest some time and take a look at this part of the mysql site

It may hurt at first but once you get the hang of it you'll be fine

HTH

neveriwas

I have read it and re-read it, I have referance about 50 other pages of terminology that it jabbers on about, and I have gotten no where. Primarily this is because of the fact that I need some familiar ground to refer to in order to learn and understand the concept.