Huge datasets - eg High number of Members

gus

I've put together a pretty 'standard' mysql table of users for a website. The login script checks the database for username/password etc. I use this script for several small websites I run.

Might be a silly question, but if I design a website that becomes monumentally popular and suddenly I have one million members (as an extreme example), is it feasible to keep all these members in the one 'Members' table? Would the query to check username/password take far too long to search through 1m records?

I'm curious - Friends Reunited or Facebook, for example, will have this issue so wonder how they do it? Would they split the table into smaller tables, somehow?

I'm a bit of a novice at this so would appreciate any advice.

Thanks.

tomhath

With indexes on the table and high-end hardware, a million rows is no problem at all for a database.

void_function

If you get a million users, then you will have other problems which will definitely require you to have load balancing and database clusters. In fact, if you're on shared hosting, you should consider going to dedicated beginning with 15-30 thousand visits per day.

At any rate, consider having fixed-size tables for users. It is better to waste a few bytes per field using CHAR instead of VARCHAR, than having the slowdown of variable sized rows. I recommend this even for small user base.

tomhath

void_function wrote:
It is better to waste a few bytes per field using CHAR instead of VARCHAR, than having the slowdown of variable sized rows. I recommend this even for small user base.

I've never heard this advice before. Getting the data from the disk into memory is always the slowest step. I would expect varchar to be faster.

void_function

Well, varchar can be faster only with regards to smaller data being read faster, but on few bytes of difference, per row, this is negligible. The speed of fixed-sized rows over variable ones is visible in sorting and going through rows to find the ones to select. Indices are not perfect, not always welcome, and there is always some searching through the rows.

This advice is also given in the MySQL documentation:

http://dev.mysql.com/doc/refman/5.0/en/data-size.html

laserlight

This advice is also given in the MySQL documentation:

I think you are mistaken. The MySQL documentation you linked to merely states that there will be a trade-off of space for time when choosing CHAR over VARCHAR. It also states that reducing space usage is a basic optimisation technique. It does not advise always choosing CHAR over VARCHAR, unlike what you have done. Presumably, such a choice is left to the user's discretion, since if it is significant, profiling would show which is better for the specific case that the user must handle.

void_function

The trade off is exactly what I was referring to. Besides, I said that fixed-sized rows can be faster (and in majority of cases ARE faster), not that they are absolutely faster always. 😉

EDIT: The best way to be sure, on the case-by-case basis, is to run benchmarks for that particular application. Make a script that generates hundreds of thousands of user data rows, and test between fixed and variable sized fields, and indexing.