Re: Poor performance on 4GB system

[Tobias]

Thanks for helping! Here's the schema for all four tables:

mysql> DESCRIBE forum_post_text;
+-------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| post_text_id | int(10) unsigned | | PRI | 0 | |
| post_text_subject | varchar(255) | | | | |
| post_text | text | YES | | NULL | |
| post_id | int(10) unsigned | | MUL | 0 | |
+-------------------+------------------+------+-----+---------+-------+

mysql> SHOW INDEXES FROM forum_post_text;
+-----------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| forum_post_text | 0 | PRIMARY | 1 | post_text_id | A | 1577229 | NULL | NULL | | BTREE | |
| forum_post_text | 1 | post_id | 1 | post_id | A | 1577229 | NULL | NULL | | BTREE | |
+-----------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> DESCRIBE forum_post;
+--------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------------------+-------+
| post_id | int(10) unsigned | | PRI | 0 | |
| post_nickname | varchar(25) | YES | | NULL | |
| post_user_id | int(10) unsigned | | MUL | 0 | |
| post_user_ip | varchar(15) | YES | | NULL | |
| post_date_posted | datetime | | MUL | 0000-00-00 00:00:00 | |
| post_date_modified | timestamp(14) | YES | | NULL | |
| post_board_id | tinyint(3) unsigned | | | 0 | |
| post_thread_id | int(10) unsigned | | MUL | 0 | |
| post_parent_id | int(10) unsigned | | MUL | 0 | |
| post_icon_id | tinyint(3) unsigned | | | 0 | |
| post_inform_on_answer | enum('0','1') | | | 0 | |
| post_author_is_moderator | enum('0','1') | | | 0 | |
| post_moved_board_id | int(10) unsigned | YES | | NULL | |
| post_moved_post_id | int(10) unsigned | YES | | NULL | |
+--------------------------+---------------------+------+-----+---------------------+-------+

mysql> SHOW INDEXES FROM forum_post;
+------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| forum_post | 0 | PRIMARY | 1 | post_id | A | 1577215 | NULL | NULL | | BTREE | |
| forum_post | 1 | IDX_forum_posts_user | 1 | post_user_id | A | 58415 | NULL | NULL | | BTREE | |
| forum_post | 1 | IDX_forum_posts_date | 1 | post_date_posted | A | 1577215 | NULL | NULL | | BTREE | |
| forum_post | 1 | post_parent_id | 1 | post_parent_id | A | 788607 | NULL | NULL | | BTREE | |
| forum_post | 1 | post_thread_id | 1 | post_thread_id | A | 262869 | NULL | NULL | | BTREE | |
+------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+


mysql> DESCRIBE forum_thread;
+----------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------------------+-------+
| thread_id | int(10) unsigned | | PRI | 0 | |
| thread_subject | varchar(255) | | | | |
| thread_user_id | int(10) unsigned | YES | | NULL | |
| thread_replies | int(10) unsigned | YES | | 0 | |
| thread_date_posted | datetime | | | 0000-00-00 00:00:00 | |
| thread_board_id | tinyint(3) unsigned | | MUL | 0 | |
| thread_first_post_id | int(10) unsigned | YES | MUL | NULL | |
| thread_last_post_id | int(10) unsigned | YES | MUL | NULL | |
| thread_icon_id | tinyint(3) unsigned | | | 0 | |
| thread_is_locked | enum('0','1') | YES | | 0 | |
+----------------------+---------------------+------+-----+---------------------+-------+


mysql> SHOW INDEXES FROM forum_thread;
+--------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
| forum_thread | 0 | PRIMARY | 1 | thread_id | A | 278001 | NULL | NULL | | BTREE | |
| forum_thread | 1 | thread_first_post_id | 1 | thread_first_post_id | A | 278001 | NULL | NULL | YES | BTREE | |
| forum_thread | 1 | thread_last_post_id | 1 | thread_last_post_id | A | 278001 | NULL | NULL | YES | BTREE | |
| forum_thread | 1 | thread_board_id | 1 | thread_board_id | A | 147 | NULL | NULL | | BTREE | |
+--------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+


mysql> DESCRIBE user;
+----------------------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| username | varchar(20) | | UNI | | |
| password | varchar(20) | YES | | NULL | |
| name | varchar(70) | YES | | NULL | |
| vorname | varchar(30) | YES | | NULL | |
| dateregistered | datetime | YES | | NULL | |
| lastvisit | datetime | YES | | NULL | |
| email | varchar(60) | | | | |
| birthday | varchar(14) | | | | |
| activate_flag | enum('0','1','2') | | | 0 | |
| activate_code | varchar(16) | | MUL | | |
| date_modified | timestamp(14) | YES | | NULL | |
| lang | char(2) | | MUL | | |
+----------------------+-------------------+------+-----+---------+----------------+


mysql> SHOW INDEXES FROM user;
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| user | 0 | PRIMARY | 1 | id | A | 223684 | NULL | NULL | | BTREE | |
| user | 0 | idx_username | 1 | username | A | 223684 | NULL | NULL | | BTREE | |
| user | 1 | lang | 1 | lang | A | 2 | NULL | NULL | | BTREE | |
| user | 1 | activate_code | 1 | activate_code | A | 223684 | NULL | NULL | | BTREE | |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+


Again, thanks a lot for your help!

--
Toby