BUILD : 1.7.19 (1299) BUGS : NOTES : Added MySQL indexes to decrease the MySQL load during database operations

git-svn-id: svn://svn.anope.org/anope/trunk@1299 31f1291d-b8d6-0310-a050-a5561fc1590b


git-svn-id: http://anope.svn.sourceforge.net/svnroot/anope/trunk@1017 5417fbe8-f217-4b02-8779-1006273d7864

1 files changed, 191 insertions, 141 deletions

diff --git a/data/tables.sql b/data/tables.sql
index 546c95287..2d4889cb5 100644
--- a/data/tables.sql
+++ b/data/tables.sql
@@ -1,27 +1,18 @@
 
 -- If you need to create your db, uncomment the following lines.
--- create database anope;
--- use anope;
+-- 
+-- CREATE DATABASE anope;
+-- USE anope;
 
---
--- Table structure for table `anope_info`
---
-
-DROP TABLE IF EXISTS anope_info;
-CREATE TABLE anope_info (
-  version int(11) default NULL,
-  date datetime default NULL
-) TYPE=MyISAM;
-
---
--- Table structure for table `anope_bs_core`
---
+-- 
+-- Table structure for table 'anope_bs_core'
+-- 
 
 DROP TABLE IF EXISTS anope_bs_core;
 CREATE TABLE anope_bs_core (
   bs_id int(11) NOT NULL auto_increment,
   nick varchar(255) NOT NULL default '',
-  user text NOT NULL,
+  `user` text NOT NULL,
   host text NOT NULL,
   rname text NOT NULL,
   flags int(11) NOT NULL default '0',
@@ -29,65 +20,75 @@ CREATE TABLE anope_bs_core (
   chancount int(11) NOT NULL default '0',
   active int(1) NOT NULL default '1',
   PRIMARY KEY  (bs_id),
-  UNIQUE KEY nick (nick),
-  KEY nick_index (nick(10))
-) TYPE=MyISAM;
+  UNIQUE KEY nick (nick)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
---
--- Table structure for table `anope_cs_access`
---
+-- --------------------------------------------------------
+
+-- 
+-- Table structure for table 'anope_cs_access'
+-- 
 
 DROP TABLE IF EXISTS anope_cs_access;
 CREATE TABLE anope_cs_access (
   ca_id int(11) NOT NULL auto_increment,
   in_use int(11) NOT NULL default '0',
-  level int(11) NOT NULL default '0',
+  `level` int(11) NOT NULL default '0',
   display varchar(255) NOT NULL default '',
   channel varchar(255) NOT NULL default '',
   last_seen int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (ca_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (ca_id),
+  UNIQUE KEY channel (channel,display)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
 
---
--- Table structure for table `anope_cs_akicks`
---
+-- 
+-- Table structure for table 'anope_cs_akicks'
+-- 
 
 DROP TABLE IF EXISTS anope_cs_akicks;
 CREATE TABLE anope_cs_akicks (
   ck_id int(11) NOT NULL auto_increment,
   channel varchar(255) NOT NULL default '',
   flags int(11) NOT NULL default '0',
-  dmask text NOT NULL,
+  dmask varchar(255) NOT NULL,
   reason text NOT NULL,
   creator text NOT NULL,
   addtime int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (ck_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (ck_id),
+  UNIQUE KEY channel (channel,dmask)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
---
--- Table structure for table `anope_cs_badwords`
---
+-- --------------------------------------------------------
+
+-- 
+-- Table structure for table 'anope_cs_badwords'
+-- 
 
 DROP TABLE IF EXISTS anope_cs_badwords;
 CREATE TABLE anope_cs_badwords (
   cw_id int(11) NOT NULL auto_increment,
   channel varchar(255) NOT NULL default '',
-  word text NOT NULL,
-  type int(11) NOT NULL default '0',
+  word varchar(255) NOT NULL,
+  `type` int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (cw_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (cw_id),
+  UNIQUE KEY channel (channel,word)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
 
---
--- Table structure for table `anope_cs_info`
---
+-- 
+-- Table structure for table 'anope_cs_info'
+-- 
 
 DROP TABLE IF EXISTS anope_cs_info;
 CREATE TABLE anope_cs_info (
   ci_id int(11) NOT NULL auto_increment,
-  name varchar(255) NOT NULL default '',
+  `name` varchar(255) NOT NULL default '',
   founder text NOT NULL,
   successor text NOT NULL,
   founderpass tinyblob NOT NULL,
@@ -123,40 +124,48 @@ CREATE TABLE anope_cs_info (
   repeattimes smallint(6) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
   PRIMARY KEY  (ci_id),
-  UNIQUE KEY name (name),
-  KEY name_index (name(10))
-) TYPE=MyISAM;
+  UNIQUE KEY `name` (`name`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
---
--- Table structure for table `anope_cs_levels`
---
+-- --------------------------------------------------------
+
+-- 
+-- Table structure for table 'anope_cs_levels'
+-- 
 
 DROP TABLE IF EXISTS anope_cs_levels;
 CREATE TABLE anope_cs_levels (
   cl_id int(11) NOT NULL auto_increment,
   channel varchar(255) NOT NULL default '',
   position int(11) NOT NULL default '0',
-  level int(11) NOT NULL default '0',
+  `level` int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (cl_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (cl_id),
+  UNIQUE KEY channel (channel,position)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+-- 
+-- Table structure for table 'anope_cs_ttb'
+-- 
 
---
--- Table structure for table `anope_cs_ttb`
---
 DROP TABLE IF EXISTS anope_cs_ttb;
 CREATE TABLE anope_cs_ttb (
   ct_id int(11) NOT NULL auto_increment,
   channel varchar(255) NOT NULL default '',
   ttb_id int(11) NOT NULL default '0',
-  value int(11) NOT NULL default '0',
+  `value` int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (ct_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (ct_id),
+  UNIQUE KEY channel (channel,ttb_id)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
 
---
--- Table structure for table `anope_hs_core`
---
+-- 
+-- Table structure for table 'anope_hs_core'
+-- 
 
 DROP TABLE IF EXISTS anope_hs_core;
 CREATE TABLE anope_hs_core (
@@ -165,34 +174,51 @@ CREATE TABLE anope_hs_core (
   vident text NOT NULL,
   vhost text NOT NULL,
   creator text NOT NULL,
-  time int(11) NOT NULL default '0',
+  `time` int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
   PRIMARY KEY  (bs_id),
-  UNIQUE KEY nick (nick),
-  KEY nick_index (nick(10))
-) TYPE=MyISAM;
+  UNIQUE KEY nick (nick)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
---
--- Table structure for table `anope_ms_info`
---
+-- --------------------------------------------------------
+
+-- 
+-- Table structure for table 'anope_info'
+-- 
+
+DROP TABLE IF EXISTS anope_info;
+CREATE TABLE anope_info (
+  version int(11) default NULL,
+  `date` datetime default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+-- 
+-- Table structure for table 'anope_ms_info'
+-- 
 
 DROP TABLE IF EXISTS anope_ms_info;
 CREATE TABLE anope_ms_info (
   nm_id int(11) NOT NULL auto_increment,
-  receiver text NOT NULL,
+  receiver varchar(255) NOT NULL,
   number int(11) NOT NULL default '0',
   flags int(11) NOT NULL default '0',
-  time int(11) NOT NULL default '0',
+  `time` int(11) NOT NULL default '0',
   sender text NOT NULL,
-  text blob NOT NULL,
+  `text` blob NOT NULL,
   serv enum('NICK','CHAN') NOT NULL default 'NICK',
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (nm_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (nm_id),
+  UNIQUE KEY nm_id (nm_id,serv),
+  KEY receiver (receiver,serv)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
 
---
--- Table structure for table `anope_ns_access`
---
+-- 
+-- Table structure for table 'anope_ns_access'
+-- 
 
 DROP TABLE IF EXISTS anope_ns_access;
 CREATE TABLE anope_ns_access (
@@ -200,12 +226,15 @@ CREATE TABLE anope_ns_access (
   display varchar(255) NOT NULL default '',
   access text NOT NULL,
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (na_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (na_id),
+  UNIQUE KEY display (display)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
 
---
--- Table structure for table `anope_ns_alias`
---
+-- 
+-- Table structure for table 'anope_ns_alias'
+-- 
 
 DROP TABLE IF EXISTS anope_ns_alias;
 CREATE TABLE anope_ns_alias (
@@ -214,19 +243,20 @@ CREATE TABLE anope_ns_alias (
   nick varchar(255) NOT NULL default '',
   time_registered int(10) unsigned NOT NULL default '0',
   last_seen int(10) unsigned NOT NULL default '0',
-  status int(11) unsigned NOT NULL default '0',
+  `status` int(11) unsigned NOT NULL default '0',
   last_usermask text NOT NULL,
   last_realname text NOT NULL,
   last_quit text NOT NULL,
   active tinyint(1) NOT NULL default '1',
   PRIMARY KEY  (na_id),
-  UNIQUE KEY nick (nick),
-  KEY nick_index (nick(10))
-) TYPE=MyISAM;
+  UNIQUE KEY nick (nick)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
---
--- Table structure for table `anope_ns_core`
---
+-- --------------------------------------------------------
+
+-- 
+-- Table structure for table 'anope_ns_core'
+-- 
 
 DROP TABLE IF EXISTS anope_ns_core;
 CREATE TABLE anope_ns_core (
@@ -237,7 +267,7 @@ CREATE TABLE anope_ns_core (
   icq int(10) unsigned NOT NULL default '0',
   url text NOT NULL,
   flags int(11) NOT NULL default '0',
-  language smallint(5) unsigned NOT NULL default '0',
+  `language` smallint(5) unsigned NOT NULL default '0',
   accesscount smallint(6) NOT NULL default '0',
   memocount smallint(5) unsigned NOT NULL default '0',
   memomax smallint(5) unsigned NOT NULL default '0',
@@ -246,48 +276,53 @@ CREATE TABLE anope_ns_core (
   greet text NOT NULL,
   active tinyint(1) NOT NULL default '1',
   PRIMARY KEY  (nc_id),
-  UNIQUE KEY display (display),
-  KEY display_index (display(10))
-) TYPE=MyISAM;
+  UNIQUE KEY display (display)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
 
---
--- Table structure for table `anope_ns_request`
---
+-- 
+-- Table structure for table 'anope_ns_request'
+-- 
 
 DROP TABLE IF EXISTS anope_ns_request;
 CREATE TABLE anope_ns_request (
   nr_id int(11) NOT NULL auto_increment,
   nick varchar(255) NOT NULL default '',
   passcode text NOT NULL,
-  password tinyblob NOT NULL,
+  `password` tinyblob NOT NULL,
   email text NOT NULL,
   requested int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
   PRIMARY KEY  (nr_id),
-  UNIQUE KEY nick (nick),
-  KEY nick_index (nick(10))
-) TYPE=MyISAM;
+  UNIQUE KEY nick (nick)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
---
--- Table structure for table `anope_os_akills`
---
+-- --------------------------------------------------------
+
+-- 
+-- Table structure for table 'anope_os_akills'
+-- 
 
 DROP TABLE IF EXISTS anope_os_akills;
 CREATE TABLE anope_os_akills (
   ok_id int(11) NOT NULL auto_increment,
-  user text NOT NULL,
-  host text NOT NULL,
+  `user` varchar(255) NOT NULL,
+  host varchar(255) NOT NULL,
   xby text NOT NULL,
   reason text NOT NULL,
   seton int(11) NOT NULL default '0',
   expire int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (ok_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (ok_id),
+  UNIQUE KEY `user` (`user`,host)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
---
--- Table structure for table `anope_os_core`
---
+-- --------------------------------------------------------
+
+-- 
+-- Table structure for table 'anope_os_core'
+-- 
 
 DROP TABLE IF EXISTS anope_os_core;
 CREATE TABLE anope_os_core (
@@ -299,85 +334,100 @@ CREATE TABLE anope_os_core (
   sqlines_count int(11) NOT NULL default '0',
   szlines_count int(11) NOT NULL default '0',
   PRIMARY KEY  (oc_id)
-) TYPE=MyISAM;
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
 
---
--- Table structure for table `anope_os_exceptions`
---
+-- 
+-- Table structure for table 'anope_os_exceptions'
+-- 
 
 DROP TABLE IF EXISTS anope_os_exceptions;
 CREATE TABLE anope_os_exceptions (
   oe_id int(11) NOT NULL auto_increment,
-  mask text NOT NULL,
+  mask varchar(255) NOT NULL,
   lim int(11) NOT NULL default '0',
   who text NOT NULL,
   reason text NOT NULL,
-  time int(11) NOT NULL default '0',
+  `time` int(11) NOT NULL default '0',
   expires int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (oe_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (oe_id),
+  UNIQUE KEY mask (mask)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
---
--- Table structure for table `anope_os_news`
---
+-- --------------------------------------------------------
+
+-- 
+-- Table structure for table 'anope_os_news'
+-- 
 
 DROP TABLE IF EXISTS anope_os_news;
 CREATE TABLE anope_os_news (
   on_id int(11) NOT NULL auto_increment,
-  type int(11) NOT NULL default '0',
+  `type` int(11) NOT NULL default '0',
   num int(11) NOT NULL default '0',
   ntext text NOT NULL,
   who text NOT NULL,
-  time int(11) NOT NULL default '0',
+  `time` int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (on_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (on_id),
+  UNIQUE KEY `type` (`type`,num,`time`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
 
---
--- Table structure for table `anope_os_sglines`
---
+-- 
+-- Table structure for table 'anope_os_sglines'
+-- 
 
 DROP TABLE IF EXISTS anope_os_sglines;
 CREATE TABLE anope_os_sglines (
   og_id int(11) NOT NULL auto_increment,
-  mask text NOT NULL,
+  mask varchar(255) NOT NULL,
   xby text NOT NULL,
   reason text NOT NULL,
   seton int(11) NOT NULL default '0',
   expire int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (og_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (og_id),
+  UNIQUE KEY mask (mask)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
---
--- Table structure for table `anope_os_sqlines`
---
+-- --------------------------------------------------------
+
+-- 
+-- Table structure for table 'anope_os_sqlines'
+-- 
 
 DROP TABLE IF EXISTS anope_os_sqlines;
 CREATE TABLE anope_os_sqlines (
   og_id int(11) NOT NULL auto_increment,
-  mask text NOT NULL,
+  mask varchar(255) NOT NULL,
   xby text NOT NULL,
   reason text NOT NULL,
   seton int(11) NOT NULL default '0',
   expire int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (og_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (og_id),
+  UNIQUE KEY mask (mask)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
 
---
--- Table structure for table `anope_os_szlines`
---
+-- 
+-- Table structure for table 'anope_os_szlines'
+-- 
 
 DROP TABLE IF EXISTS anope_os_szlines;
 CREATE TABLE anope_os_szlines (
   og_id int(11) NOT NULL auto_increment,
-  mask text NOT NULL,
+  mask varchar(255) NOT NULL,
   xby text NOT NULL,
   reason text NOT NULL,
   seton int(11) NOT NULL default '0',
   expire int(11) NOT NULL default '0',
   active tinyint(1) NOT NULL default '1',
-  PRIMARY KEY  (og_id)
-) TYPE=MyISAM;
+  PRIMARY KEY  (og_id),
+  UNIQUE KEY mask (mask)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;