Advanced MySQL Joining. Speeding up query

问题:

I have a list of users on my php application (using codeigniter). Each user may have completed a form with about 1000 or so total fields. The structure looks similar to this:

users

id|username|...

completed_form_fields

id|formid|userid|fieldkey|data

where field key is just a unique key for that particular form field, ie: "first_name"

I have a user search page where people can filter out specific users by the fields they chose (eye color, race, gender...) Then I need to display these fields so I would love (and currently have) an output like this:

$filteredmembers = array(
[0] = Object(
   [id] => 1
   [username] => kilrizzy
...
   [fields] => Array(
        [fname] => Jeff
        [gender] => Male
        ...

Currently my script is obviously taking forever since I query all the members who filled out this form, then loop through each one to query all of their fields. THEN filter those out based on criteria + page / offset.

I know there needs to be a way to join these together in one query I am not familiar with

Simplified version of my very slow code:

function get_members(){
    $this->db->select('u.*');
    $this->db->from('users AS u');
    $query = $this->db->get();
    if ($query->num_rows() > 0){
        $members = $query->result();
        //Get fields from each user
        foreach($members as $mk => $mv){
            $fields = $this->get_form_fields($mv->id,1,true);
            $members[$mk]->fields = $fields;
        }
        return $members;
    }else{
        return false;   
    }
}
function get_form_fields($uid,$form,$values=false){
    $this->db->where('user', $uid); 
    $this->db->where('form', $form); 
    $query = $this->db->get('form_fields');
    if ($query->num_rows() > 0){
        $result = $query->result();
        return $result;
    }else{
        return false;   
    }
}

回答1:

There is a way but it gets over expensive the more you add fields. The same thing occurs with many CMS that choose to store additionnal user data in that form.

You can get a working search SQL using this:

SELECT
    users.*,
    firstname.data AS firstname,
    lastname.data AS lastname,
    eyecolor.data AS eyecolor,

FROM
    users
    LEFT JOIN completed_form_fields AS firstname ON firstname.userid = users.id AND firstname.fieldkey = "firstname"
    LEFT JOIN completed_form_fields AS lastname ON lastname.userid = users.id AND lastname.fieldkey = "lastname"
    LEFT JOIN completed_form_fields AS eyecolor ON eyecolor.userid = users.id AND eyecolor.fieldkey = "eyecolor"

WHERE
    firstname.data LIKE '%searchdata%'
    OR lastname.data LIKE '%searchdata%'
    OR eyecolor.data LIKE '%searchdata%'

This method gets very big and expensive for the MySQL server the more you add tables. Therefore, i would recommend not to go more than 10-15 joins like that and then again, i'd profile it to make sure.

回答2:

SELECT u.username, f.formid, f.userid, f.fieldkey, f.data FROM user AS u
LEFT JOIN completed_form_fields AS f
ON f.userid = u.id


you should look at indexing your userid, index(userid), via phpmyadmin or sql file

mysql-indexes

回答3:

I'm not sure I completely understand the problem. You can use a single query to get all users, and all of their fields (basically, this is what Philip suggested):

SELECT u.username, f.* 
FROM user AS u
    INNER JOIN completed_form_fields AS f
    ON f.userid = u.id
ORDER BY u.id, f.fieldkey

To filter the results, add a WHERE clause with the conditions. For example, to only get data from fieldkeys 'k1', 'k2' and 'k3':

SELECT u.username, f.* 
FROM user AS u
    INNER JOIN completed_form_fields AS f
    ON f.userid = u.id
WHERE f.fieldkey IN ('k1', 'k2', 'k3')
ORDER BY u.id, f.fieldkey

Is this what you're looking for?