When it comes to querying, indexing of a table should never be your first concern.
The queries you plan to use should dictate the indexes you need.
Based on the queries, some columns can be individually indexed. Other queries require compound indexes. The ORDER BY and GROUP BY clauses should provide immediate hints for indexes to make. Not using such hints may result in temp table sorting rather than using the indexes for data in the desired order needed.
Low cardinality of column values should eliminate the need for an index.
Even with these things taken into consideration, you may find that query may need some adjustment (a.k.a. refactoring) for performance gains.
When you reach the point of having the right indexes, not you have to worry about the size of those indexes. For a MyISAM table, this would mean that the .MYI file may grow significantly.
The size of the index file as well as the number of indexes should now be weighed against the performance of your queries, especially if the indexes provide the proper ordering of data and fastest retrieval.
Explain plans for queries may change over time depending on the number of rows, cardinality of columns, number of DELETEs and UPDATEs. Once a query's explain plan changes from what it looked like months ago, you should explore the need to add or remove indexes.
All you need here is to make the username a unique index and MySQL will (generally) handle this very well.
Unfortunately, I had to write 'generally' because there can be many many other factors on your system that could slow this down. I recommend having MySQL 5.5 and the table be InnoDB. Proper indexing and configuration will go a long way in helping you.
The short of it is, MySQL/InnoDB is easily able to handle 10 million rows.
Best Answer
If your data can be pre-calculated and is static intra-day then why not?
After all, isn't this what a data warehouse does?
As for having up to 50 million rows, this is peanuts.