Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm currently working on a database schema for tables to hold user information on a website with integrated forums.

Right now my idea is to have all the data in two separate tables: one table with "essential" information stored with the InnoDB engine (for transactions and crash-recovery) while "nonessential" information is stored in a regular MyISAM table:

CREATE TABLE users_data (
    user_id        int unsigned NOT NULL AUTO_INCREMENT,
    username       varchar(24) UNIQUE NOT NULL,
    password       char(32) NOT NULL,
    name           varchar(64) NOT NULL DEFAULT '',
    gender         enum('M','F','U') NOT NULL DEFAULT 'U',
    birth_date     date NOT NULL,
    email          varchar(256) NOT NULL,
    member_group   enum('Banned','Member','Moderator','Admin'),
    join_date      date NOT NULL,
    avatar         varchar(40) NOT NULL DEFAULT '',
    views          int NOT NULL DEFAULT '0',
    posts          int NOT NULL DEFAULT '0',
    ip             int unsigned NOT NULL DEFAULT '0',
    last_seen      timestamp NOT NULL,
    PRIMARY KEY (user_id),
    UNIQUE KEY email (email),
    KEY     join_date_key (join_date, username),
    KEY     post_count_key (posts, username),
    KEY     last_seen (last_seen)
) ENGINE=InnoDB

CREATE TABLE users_profile (
    user_id        int unsigned NOT NULL,
    location       varchar(64),
    interests      varchar(500),
    website        varchar(128),
    msn            varchar(256),
    aim            varchar(256),
    facebook       varchar(50),
    signature      varchar(500) NOT NULL DEFAULT '',
    PRIMARY KEY (user_id)
)

With the tables separated like this, I get high concurrency on the InnoDB table, which is where information is most frequently going to be selected and inserted, while most of the arbitrary text that a user can store for their profile is in a separate MyISAM table. Also, because the MyISAM table does not require a row for every user, there is less wasted space from the allocation of space for the large varchar fields.

One of the factors that influenced this organization is that the data in users_profile is not accessed very frequently. My instinct is that it would then be helpful to disable the cache for this table so that more memory may be allocated for other purposes such as indices on MyISAM tables and other caches. If my conclusion is correct, how would I go about doing this?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

InnoDB and MyISAM already use different buffer/cache structures, so if you use different engines for the two tables it is very easy to configure different cache sizes.

For InnoDB you set up innodb_buffer_pool_size which caches data and indexes. For MyISAM you configure key_buffer_size for buffering indexes. MySQL leaves MyISAM data caching to the OS to manage. You could also create custom key buffers for each MyISAM table and decide how big the cache is.

share|improve this answer
    
Great explanation, thank you! –  JasonSage Oct 31 '11 at 9:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.