Database

Table Structure

  1. acl 
  2. auth_token
  3. cache
  4. category
  5. category_page
  6. config
  7. file
  8. file_link
  9. log 
  10. menu
  11. page
    1. 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
  12. page_link
    1. a reference to internal Wiki page
  13. rating
  14. referrer
  15. revision
  16. poll
  17. user
  18. user_setting
  19. usergroup
  20. usergroup_member
  21. watch

Access MySQL via command line

mysql -h 127.0.0.1 -P 3306 -u root -p <database>

mysql> DESCRIBE table_name;

Database Diagram

0 of 0 Files accessible from this page :

There are no attached files to display.


-> MySQL Workbench

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

Table Details

Field Type Null Key Default Extra

+------------------+---------------------+------+-----+---------+----------------+
| 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       |                |
+------------------+---------------------+------+-----+---------+----------------+

Read comments (2 comments)