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.

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?

share|improve this question
1  
Adding this as just a comment as it's not a direct answer to your question. Maybe this is what you are doing beyond your sample query but it is very very bad practice to store the actual passwords in your database. You want to store them as a has and then query like where password_hash=hash($userEnteredPassword) –  atxdba Dec 30 '11 at 23:00
    
@atxdba I actually hashed them, but here I just gave an example. –  john.locke Dec 31 '11 at 3:22
add comment

4 Answers

up vote 7 down vote accepted

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

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

share|improve this answer
    
Actually I use InnoDB storage engine, but I think the cache process would be fine with it, isn't it @RolandoMySQLDBA ? –  john.locke Dec 31 '11 at 3:18
    
No. The steps in my answer is MyISAM only. –  RolandoMySQLDBA Dec 31 '11 at 3:21
    
If the users table is involved in transactions, then I need to submit another answer based solely on InnoDB. –  RolandoMySQLDBA Dec 31 '11 at 3:22
    
Sorry I did not mention that, I didn't know they'd have different approaches! –  john.locke Dec 31 '11 at 3:24
    
I answered based on MyISAM because I wanted the users table to be cached in its own key buffer. –  RolandoMySQLDBA Dec 31 '11 at 3:25
show 5 more comments

If you use Mysql 5.1 and higher, you can try partitioning your table.
As of your question about whether it speeds up login process, it depends on how the rest of login procedure looks like (for instance, if your query now takes 0.05 seconds, and rest of the code takes 20 secs, I'd rather re-think the whole routine...).
Also, regardless of using partitions, don't forget to add indexes as RolandoMySQLDBA pointed out.

share|improve this answer
    
Good call for determining the actual cause of a performance problem before optimizing. It is often not where we think it is. Evidence based tuning is the way to go! –  Stuart Woodward Jan 4 '12 at 22:02
add comment

A table of a couple million rows shouldn't need to be split up. Performance tuning should be done through indexes. MySpace had hundreds of millions of accounts listed in a single table and performance on that table was just fine. (I was a DBA for MySpace at the height of their usage.) The table in that case was probably 80-90 bytes wide (maybe a little more).

share|improve this answer
add comment

Do you actually have 2 million users? Unless you already have this problem or are certain that you will, you are optimizing way ahead of time. Add a compound index on login and password fields and be done with it. Don't optimize unless you know you actually have a problem to solve. I'm certain you have bigger problems to solve.

share|improve this answer
    
What do you mean by "you are certain I have bigger problems to solve"? –  john.locke Jan 1 '12 at 2:56
    
It doesn't make sense to solve problems when we know in the near future we'll encounter with lots of problems. This troubleshooting is a headache when there are lots of data in the table! -1 for you. –  john.locke Jan 1 '12 at 3:27
1  
+1 for legit comments and to negate john.locke's hate –  atxdba Jan 1 '12 at 5:26
2  
My point is twofold...don't optimize before you have to & 2 million records is not very many. An index will be plenty. –  Aaron Brown Jan 1 '12 at 13:47
add comment

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.