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?
Best Answer
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.