高级MySQL加入。加快查询速度 [英] Advanced MySQL Joining. Speeding up query

问题描述

我有一个用户列表在我的PHP应用程序（使用codeigniter）。每个用户可能已经完成了具有大约1000个左右的总字段的表单。结构与此类似：

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:

用户

id|username|...

completed_form_fields p>

completed_form_fields

id|formid|userid|fieldkey|data

其中字段键只是特定表单字段的唯一键，即：first_name

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;   
    }
}

推荐答案

是一种方式，但它越过昂贵的越多你添加字段。

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.

您可以使用以下命令获得有效的搜索SQL：

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%'

这种方法对于MySQL服务器来说非常大和昂贵，你添加表越多。因此，我建议不要超过10-15加入像这样，然后再次，我会配置它，以确保。

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.

这篇关于高级MySQL加入。加快查询速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！