Best approach for storing huge data

Question

I'll be handling 300,000+ users. Each user (after logging in) will be able to create lets say "folders", in each folder, users will create "name=value,name2=value2,name3=value3" entry pairs. Each name won't be more than 260 chars and each value won't be more than 1024 bytes. All readable characters.

My ideas for possible approaches:

1. Create tables called userentries_[first letter of username] so there will be 26 tables, each table will have entries and user ID. So when I want to retrieve user data, I'll do

   select * from userentries_[firstletter] where userid= $UID"
   

2. Create a single table to hold all information.

3. Create a table called userdata, here I'll have UID and FileName. So when user wants to retrieve data, I'll read database to find the name of file, then open FileName in PHP, create an XML/JSON response and client (which will be android/ios/desktop app) process it.

Which one is the best and most optimized (speed/performance vise) ?

P.S. name=value pairs etc. won't be searchable, even if user supposed to search through them, it will be in client-side, definitely not on DB

Answer 1

You might use three total tables (adjust table/column names and SQL dialect to taste).

The first is the user table, which contains the master list of user_id's.

The second is the folder table, that will contain a list of folders for users.

|  folders         |
|------------------|
| id (int)         |
| user_id (int)    |
| name (varchar)   |

And a third for the name/value pairs

|  user_data       |
|------------------|
| id (int)         |
| folder_id (int)  |
| name (varchar)   |
| value (varchar)  |

To query for the name/values in a specific folder then, you'd do something like:

select name, value from user_data where folder_id = ?

To query all data for a user it can be something like:

select user_data.name, user_data.value 
  from folders
  join user_data on (user_data.folder_id = folders.id) 
  where folders.user_id = ?

Alternatively, denormalize the user_data table slightly and add the user_id in there as well. That will simplify queries for all of the user's data, but you'll have to manually keep the keys in sync.

Properly indexed, this will work fine for large data sets.

Answer 2

The question can be reworded as "I have the canonical perfect application for a nosql key-value store, what should I do?". Yes, this can be done with sql, but a nosql implementation is likely to be more efficient.

Look at systems like riak, apache cassandra, berkeley db or memcachedb, or if you're planning on deploying to amazon web services dynamodb. Or perhaps consider a more traditional database with either memcache or redis as a cacheing layer.

Answer 3

You may want to consider not using SQL at all, but use something like Azure table storage.

Troy Hunt has an interesting article on its performance on huge datasets: http://www.troyhunt.com/2014/12/applied-azure-infographic-of-how-have-i.html