Database
Table Structure
- acl
- auth_token
- cache
- category
- category_assignment
- config
- file
- file_link
- log
- menu
- page
- the latest versions of the pages and also stored comments, distinct from ordinary pages of comments that, comments that filled the fields and comment_on_id
- page_link
- a reference to internal Wiki page
- rating
- referrer
- revision
- poll
- user
- user_setting
- usergroup
- usergroup_member
- watch
Access MySQL via command line
mysql -h 127.0.0.1 -P 3306 -u root -p <database>
mysql> DESCRIBE table_name;
Database Diagram
There are no attached files to display.0 of 0 Files accessible from this page :
Database Script
Performance
Indexes
- Ensure indexes on columns used in WHERE, ON, GROUP BY clauses
- Always ensure JOIN conditions are indexed (and have identical data types)
- Be careful of the column order
- If you don't pick a primary key (bad idea!), one will be created for you -> And, you have no control over the key
Naming
Use singular for table names, e.g. “page” not “pages”
A table structure describes a specific entity
Proposed Database Schema Changes
-> R4.3-> R5.0
-> R5.5
Table Details
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
Schema summary
DESCRIBE page;
+------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------------------+------+-----+---------+----------------+ | page_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | version_id | int(10) unsigned | NO | | 1 | | | owner_id | int(10) unsigned | NO | MUL | 0 | | | user_id | int(10) unsigned | NO | MUL | 0 | | | title | varchar(250) | NO | MUL | | | | tag | varchar(250) | NO | UNI | | | | supertag | varchar(250) | NO | MUL | | | | menu_tag | varchar(250) | NO | | | | | depth | int(10) unsigned | NO | MUL | 0 | | | parent_id | int(10) unsigned | NO | | 0 | | | created | datetime | YES | MUL | NULL | | | modified | datetime | YES | MUL | NULL | | | body | mediumtext | NO | MUL | NULL | | | body_r | mediumtext | NO | | NULL | | | body_toc | text | NO | | NULL | | | formatting | varchar(20) | NO | | wacko | | | edit_note | varchar(200) | NO | | | | | minor_edit | tinyint(1) unsigned | YES | MUL | 0 | | | page_size | int(10) unsigned | NO | | 0 | | | reviewed | tinyint(1) unsigned | NO | MUL | 0 | | | reviewed_time | datetime | YES | | NULL | | | reviewer_id | int(10) unsigned | NO | | 0 | | | ip | varchar(15) | NO | | | | | latest | tinyint(1) unsigned | YES | | 1 | | | handler | varchar(30) | NO | | page | | | comment_on_id | int(10) unsigned | NO | MUL | 0 | | | comments | int(4) unsigned | NO | | 0 | | | hits | int(10) unsigned | NO | | 0 | | | theme | varchar(20) | YES | | NULL | | | page_lang | varchar(2) | NO | | | | | commented | datetime | YES | MUL | NULL | | | description | varchar(250) | NO | | | | | keywords | varchar(250) | NO | | | | | footer_comments | tinyint(1) unsigned | YES | | NULL | | | footer_files | tinyint(1) unsigned | YES | | NULL | | | footer_rating | tinyint(1) unsigned | YES | | NULL | | | hide_toc | tinyint(1) unsigned | YES | | NULL | | | hide_index | tinyint(1) unsigned | YES | | NULL | | | tree_level | tinyint(1) unsigned | NO | | 0 | | | show_menu_tag | tinyint(1) unsigned | NO | | 0 | | | allow_rawhtml | tinyint(1) unsigned | YES | | NULL | | | disable_safehtml | tinyint(1) unsigned | YES | | NULL | | | noindex | tinyint(1) unsigned | YES | | 0 | | | deleted | tinyint(1) unsigned | YES | MUL | 0 | | +------------------+---------------------+------+-----+---------+----------------+
Indices
SHOW INDEX IN page;
+----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | dev_page | 0 | PRIMARY | 1 | page_id | A | 151 | NULL | NULL | | BTREE | | | | dev_page | 0 | idx_tag | 1 | tag | A | 151 | NULL | NULL | | BTREE | | | | dev_page | 1 | idx_user_id | 1 | user_id | A | 15 | NULL | NULL | | BTREE | | | | dev_page | 1 | idx_owner_id | 1 | owner_id | A | 15 | NULL | NULL | | BTREE | | | | dev_page | 1 | idx_supertag | 1 | supertag | A | 151 | NULL | NULL | | BTREE | | | | dev_page | 1 | idx_depth | 1 | depth | A | 15 | NULL | NULL | | BTREE | | | | dev_page | 1 | idx_created | 1 | created | A | 151 | NULL | NULL | YES | BTREE | | | | dev_page | 1 | idx_modified | 1 | modified | A | 151 | NULL | NULL | YES | BTREE | | | | dev_page | 1 | idx_minor_edit | 1 | minor_edit | A | 2 | NULL | NULL | YES | BTREE | | | | dev_page | 1 | idx_deleted | 1 | deleted | A | 2 | NULL | NULL | YES | BTREE | | | | dev_page | 1 | idx_reviewed | 1 | reviewed | A | 2 | NULL | NULL | | BTREE | | | | dev_page | 1 | idx_comment_on_id | 1 | comment_on_id | A | 8 | NULL | NULL | | BTREE | | | | dev_page | 1 | idx_commented | 1 | commented | A | 151 | NULL | NULL | YES | BTREE | | | | dev_page | 1 | idx_title | 1 | title | A | 151 | NULL | NULL | | BTREE | | | | dev_page | 1 | body | 1 | body | NULL | 151 | NULL | NULL | | FULLTEXT | | | +----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+