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.

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)
–
atxdbaDec 30 '11 at 23:00

@atxdba I actually hashed them, but here I just gave an example.
–
john.lockeDec 31 '11 at 3:22

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

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

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.

What do you mean by "you are certain I have bigger problems to solve"?
–
john.lockeJan 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.lockeJan 1 '12 at 3:27

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.