-Sigh- Trying to figure out indexing in mysql.

neveriwas

This will probably be one of the two biggest tables in my database. I would like to know how it would look if proper indexing was set in place as the materials that I am reading don't make much sence to me and I have to learn the hard way.
From the bits of information that I have found, I tried to make a primary key and keys on my own, however, I don't even know what the idea behind indexing is, what it does, and how to use it with php.

ANy help would be greatly appreiciated.

-Neveriwas

# --------------------------------------------------------
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_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;
# --------------------------------------------------------

barand

Originally posted by neveriwas
I don't even know what the idea behind indexing is, what it does, and how to use it with php

Indexing does 2 jobs.

Like the index in a book, it enables the server to go straight to a record by looking up its key in an index to see where it is located in the table.

Secondly, if you regularly use an ORDER BY clause in your queries on anything other than the primary key (records are physically stored in primary key sequence) then create an index on that field. The db server will look for an index on the field(s) in the order by expression. If it finds such an index then it will use that to retrieve the records in the correct sequence, giving a huge speed increase (like the table was already sorted in that sequence)

You don't actually use the indexes in php, the database server (mySQL) uses them.

hth

neveriwas

aha! now I am beginning to see the light.

So, how do I define indexes, it's range, etc.?