MySQL – Relational Schema Problem for Friendship and Messages with Big Data Manipulation

IMHO You do not need to physically split it up. Yet, it would be nice to cache it.

If the users table uses the MyISAM Storage Engine, you have a nice advantage.

Since MyISAM only caches indexes, you could do two things

• You could create a custom key cache just to load MyISAM index for the users table only
• You could index the username and password to force the query to hit that custom key cache only

Make sure the following indexes exist for users

ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);
ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);

There are two(2) major reasons for the two indexes

REASON for index #1

The index username_ndx prevents a username from having multiple passwords, as well as prevents multiple users with the same name

REASON for index #2

The index username_password_ndx provides a covering index. Thus, your query will lookup the username and password in the custom MyISAM cache only, instead of checking the table.

More Links on the Principles of Covering Indexes

• http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
• http://ronaldbradford.com/blog/tag/covering-index/
• http://shallop.com/2011/04/covering-indexes-in-mysql/
• http://www.mysqlconf.com/mysql2009/public/schedule/detail/6796

Next thing is to actually create that custom key cache. Here are the commands to create an 8MB key cache and load that dedicated key cache (Example: If the table is mydb.users):

SET GLOBAL authentication_cache.key_buffer_size = 1024 * 1024 * 8;
CACHE INDEX mydb.users IN authentication_cache;
LOAD INDEX INTO CACHE mydb.users;

You should place these three lines in the file /var/lib/mysql/startup.sql

Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/startup.sql

This will load the cache every time mysql is started up

Give it a Try !!!

UPDATE 2011-12-30 17:25 EDT

If you would like to get the exact size to set the cache, use the following query:

SELECT CONCAT('1024 * 1024 * ',ROUND(index_length/power(1024,2))) RecommendedCacheSize
FROM information_schema.tables WHERE table_name='users';

UPDATE 2011-12-30 23:21 EDT

Here is a method based on InnoDB

You still need the indexes

ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);
ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);

You have to make sure the InnoDB Buffer Pool has the usernames and passwords available. You may have to resort to doing a full index scan upon mysql startup:

Step 1) Create ReadUserPass.sql

echo "select username,password from users;" > /var/lib/mysql/ReadUserPass.sql

Step 2) Add that script to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/ReadUserPass.sql

Step 3) Perform one of the following

• $ service mysql restart
• mysql> source /var/lib/mysql/ReadUserPass.sql

Because both of these columns (username and password) reside in the username_password_ndx, all the index pages making up this index are reloaded into the InnoDB Buffer Pool. This is necessary because there is the possiblility of the index pages being flushed out. To minimize that happening, increase the Buffer Pool Size and restart mysql (one time).

The first path is certainly the more conventional of the two, absent a compelling reason to break the system down into multiple schemas. Something along these lines:

CREATE TABLE user (
  user_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_name VARCHAR(50) NOT NULL,
  password VARCHAR(32) NOT NULL,
  status_code INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE user_file (
  file_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_id INT NOT NULL,
  path VARCHAR(2000) NOT NULL,
  INDEX (user_id),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;

CREATE TABLE user_settings (
  user_id INT PRIMARY KEY NOT NULL,
  email VARCHAR(150),
  color VARCHAR(10),
  etc VARCHAR(255),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;