Is splitting a 'users' table for authentication purposes a good idea?

Suppose I have a user table in my site in which there are around 2-3 Million users (records) in the table.

For speeding up my login process, is it a good approach to split my user table, one for their information and one for their login.

If we can run a query similar to the one below from one table:

select username,password from users where username=`test` AND password=****

Is it necessary to split it, and does this speed up my site's login process?

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).

StackExchange Database Administrators Q#9962, answer score: 10