sql - What would be the most efficient way to query large amounts of dynamic profiles in MySQL? -
i've got usersystem. every user has profile. profile fields dynamically stored in database. 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 list of users profile fields , values. i've got total of 50.000 users , 60 profile fields, resulting in 3.000.000 profile values.
what efficient way retrieve such list (and possibly perform searches based on profile fields)?
the "most efficient" way going difficult identify. try these starters:
many of values in profilevalues.profilevalue_value repeated might consider creating table holds possible strings (perhaps growing list of them) , turning field int. can indexed efficient searching without having 3m rows.
profilevalues not need pk on profilevalue_id; consider pk of (profilevalue_user_id + profilevalue_profilefield_id). ensure joins more indexed.
you need check this: what max number if store int(255) in mysql?
Comments
Post a Comment