My favorites | Sign in
Project Home Downloads Wiki Issues Source
READ-ONLY: This project has been archived. For more information see this post.
Search
for
DatabasePerformance  
Read this in case you have MySQL performance issues
Updated Feb 11, 2010 by julius.s...@gmail.com

Introduction

If you run into performance issues with a MySQL database, try using this fixed version of the db-setup.mysql script by RipZ:

db-setup.mysql

-- -- This is the required schema for MySQL. Load this into the database -- using the mysql interactive terminal: -- -- mysql> \. db-setup.mysql -- -- Then make sure you create a user in MySQL and grant it full access -- to the pyicqt database. You will need to enter this information -- into your PyICQt config file. --

CREATE DATABASE pyicqt; USE pyicqt;

-- -- registration table -- CREATE TABLE register (

owner varchar(256) NOT NULL, username TINYTEXT, password TINYTEXT, encryptedpassword TINYTEXT, UNIQUE KEY owner (owner)
);

-- -- settings table -- CREATE TABLE settings (

owner varchar(256) NOT NULL, variable TINYTEXT, value TINYTEXT
);

-- -- lists table -- CREATE TABLE lists (

owner varchar(256) NOT NULL, type TINYTEXT NOT NULL, jid TINYTEXT, KEY lists (owner)
);

-- -- list attributes table -- CREATE TABLE list_attributes (

owner varchar(256) NOT NULL, type TINYTEXT NOT NULL, jid TINYTEXT, attribute TINYTEXT, value TINYTEXT, KEY list_attributes (owner)
);

-- -- custom settings table -- CREATE TABLE csettings (

owner varchar(256) NOT NULL, variable TINYTEXT, value TINYTEXT, KEY csettings (owner)
);

-- -- x-statuses table -- CREATE TABLE xstatuses (

owner varchar(256) NOT NULL, number TINYTEXT, title TINYTEXT, value TINYTEXT, KEY xstatuses (owner)
);

Comment by v.sadi...@gmail.com, Feb 11, 2010

This is a patch:

--- db-setup.mysql 2009-08-24 16:37:59.000000000 +0400 +++ db-setup.mysql.patched 2010-02-11 13:07:22.379879491 +0300 @@ -16,17 +16,18 @@

-- registration table -- CREATE TABLE register (
- owner TEXT NOT NULL, + owner varchar(256) NOT NULL,
username TINYTEXT, password TINYTEXT,
- encryptedpassword TINYTEXT + encryptedpassword TINYTEXT, + UNIQUE KEY owner (owner)
);

-- -- settings table -- CREATE TABLE settings (

- owner TEXT NOT NULL, + owner varchar(256) NOT NULL,

variable TINYTEXT, value TINYTEXT
);
@@ -35,37 +36,41 @@
-- lists table -- CREATE TABLE lists (
- owner TEXT NOT NULL, + owner varchar(256) NOT NULL,
type TINYTEXT NOT NULL,
- jid TINYTEXT + jid TINYTEXT, + KEY lists (owner)
);

-- -- list attributes table -- CREATE TABLE list_attributes (

- owner TEXT NOT NULL, + owner varchar(256) NOT NULL,

type TINYTEXT NOT NULL, jid TINYTEXT, attribute TINYTEXT,
- value TINYTEXT + value TINYTEXT, + KEY list_attributes (owner)
);

-- -- custom settings table -- CREATE TABLE csettings (

- owner TEXT NOT NULL, + owner varchar(256) NOT NULL,

variable TINYTEXT,
- value TINYTEXT + value TINYTEXT, + KEY csettings (owner)
);

-- -- x-statuses table -- CREATE TABLE xstatuses (

- owner TEXT NOT NULL, + owner varchar(256) NOT NULL,

number TINYTEXT, title TINYTEXT,
- value TINYTEXT + value TINYTEXT, + KEY xstatuses (owner)
);

Comment by ky6uk.kun, Mar 5, 2010

bad idea use key "owner" in lists table

Comment by ky6uk.kun, Mar 5, 2010

list_attributes and csettings and xstatuses too

Powered by Google Project Hosting