What would be the most efficient way to query large amounts of dynamic profiles in MySQL?

Question

I've got a usersystem. Every user has a profile. Profile fields are dynamically stored in the database. The database looks like:

CREATE TABLE profileFields(
    profileField_id INT(255) AUTO_INCREMENT,
    profileField_name VARCHAR(255),
    profileField_value VARCHAR(255),
    profileField_type VARCHAR(255),
    profileField_isOptional TINYINT(1),
    PRIMARY KEY(profileField_id)
);
CREATE TABLE users(
    user_id INT(255) AUTO_INCREMENT,
    user_username VARCHAR(255),
    user_password VARCHAR(255),
    user_email VARCHAR(255),
    PRIMARY KEY(user_id)
);
CREATE TABLE profileValues(
    profileValue_id INT(255) AUTO_INCREMENT,
    profileValue_profileField_id INT(255),
    profileValue_user_id INT(255),
    profileValue_value VARCHAR(255),
    PRIMARY KEY(profileValue_id)
);

I'm trying to get a list of all users with all profile fields and values. I've got a total of 50.000 users and 60 profile fields, resulting in 3.000.000 profile values.

What would be the most efficient way to retrieve such a list (and possibly perform searches based on the profile fields)?

I can't see how the tables are related to each other. How are you supposed to join? 50.000 * 60 = 3.000.000, but that seems two be only two of the tables? — jarlh, Feb 05 '16 at 15:00
Do all profiles have all 60 values? because if so, you dont need to break it down further, — Takarii, Feb 05 '16 at 15:01
@jarlh looking at this information, it looks like `profileValues` is the top table, but i cant see the 60 x 50,000 part either — Takarii, Feb 05 '16 at 15:02
If you show us some ways, we'll tell you which is most efficient. — Strawberry, Feb 05 '16 at 15:59

score 0 · Accepted Answer · edited May 23 '17 at 11:52

The "most efficient" way is always going to be difficult to identify. Try these for starters:

Many of the values in profileValues.profileValue_value will be repeated so you might consider creating a table which holds all the possible strings (perhaps a growing list of them) and turning the field into an int. This can then be indexed for very efficient searching without having to do 3M rows.
profileValues does not need a PK on profileValue_id; consider a PK of (profileValue_user_id + profileValue_profileField_id). This will ensure joins are more quickly indexed.

You also need to check this: What is the MAX number if I store int(255) in MySQL?

What would be the most efficient way to query large amounts of dynamic profiles in MySQL?

1 Answers1