php - SQL内连接性能问题

Question

我安装了 WordPress，并且正在制作一个自定义插件。我的插件存储了大量的用户元数据。我使用内部联接来组合“users”和“usermeta”表中的数据，此时我将结果返回到我的 PHP 脚本。我的查询如下所示:

select
# Users table stuff
users.ID,
users.user_email,
users.display_name,

# Meta stuff

first_name.meta_value as first_name,
last_name.meta_value as last_name,
phone_number.meta_value as phone_number,
country.meta_value as country,
years_of_experience.meta_value as years_of_experience,
highest_degree_obtained.meta_value as highest_degree_obtained,
availability_for_work.meta_value as availability_for_work,
english_proficiency.meta_value as english_proficiency,
disciplines.meta_value as disciplines,
profile_picture.meta_value as profile_picture,
resume.meta_value as resume,
description.meta_value as description,
hourly_rate.meta_value as hourly_rate,
satisfaction_rating.meta_value as satisfaction_rating,
invited_projects.meta_value as invited_projects,
completed_project_count.meta_value as completed_project_count

# The table we're selecting from

from tsd_retro_users as users

# Join in our usermeta table for each individual meta value

inner join tsd_retro_usermeta first_name on users.ID = first_name.user_id
inner join tsd_retro_usermeta last_name on users.ID = last_name.user_id
inner join tsd_retro_usermeta phone_number on users.ID = phone_number.user_id
inner join tsd_retro_usermeta country on users.ID = country.user_id
inner join tsd_retro_usermeta years_of_experience on users.ID = years_of_experience.user_id
inner join tsd_retro_usermeta highest_degree_obtained on users.ID = highest_degree_obtained.user_id
inner join tsd_retro_usermeta availability_for_work on users.ID = availability_for_work.user_id
inner join tsd_retro_usermeta english_proficiency on users.ID = english_proficiency.user_id
inner join tsd_retro_usermeta disciplines on users.ID = disciplines.user_id
inner join tsd_retro_usermeta profile_picture on users.ID = profile_picture.user_id
inner join tsd_retro_usermeta resume on users.ID = resume.user_id
inner join tsd_retro_usermeta description on users.ID = description.user_id
inner join tsd_retro_usermeta hourly_rate on users.ID = hourly_rate.user_id
inner join tsd_retro_usermeta satisfaction_rating on users.ID = satisfaction_rating.user_id
inner join tsd_retro_usermeta invited_projects on users.ID = invited_projects.user_id
inner join tsd_retro_usermeta completed_project_count on users.ID = completed_project_count.user_id


# Define our select stipulations

where 
(users.ID = 20)
and
(first_name.meta_key = 'first_name')
and
(last_name.meta_key = 'last_name')
and
(phone_number.meta_key = 'phone_number')
and
(country.meta_key = 'country')
and
(years_of_experience.meta_key = 'years_of_experience')
and
(highest_degree_obtained.meta_key = 'highest_degree_obtained')
and
(availability_for_work.meta_key = 'availability_for_work')
and
(english_proficiency.meta_key = 'english_proficiency')
and
(disciplines.meta_key = 'disciplines')
and
(profile_picture.meta_key = 'profile_picture')
and
(resume.meta_key = 'resume')
and
(description.meta_key = 'description')
and
(hourly_rate.meta_key = 'hourly_rate')
and
(satisfaction_rating.meta_key = 'satisfaction_rating')
and
(invited_projects.meta_key = 'invited_projects')
and
(completed_project_count.meta_key = 'completed_project_count')

如您所见，我为尝试从数据库获取的每个值内部连接了 usermeta 表。这一切似乎工作正常，但在一定数量的内部联接之后，查询似乎慢得像爬行一样。现在，上述查询平均需要大约一秒钟，而我的用户表中只有大约二十个用户。

我的问题是:内部联接对性能有何影响？有没有更好的方法来运行上述查询并获得相同的结果？

Answer 1

试试这个方法:

select
# Users table stuff
users.ID,
users.user_email,
users.display_name,

# Meta stuff

MAX(CASE WHEN meta_table.meta_key='first_name' THEN meta_table.first_name ELSE NULL END) as first_name,
MAX(CASE WHEN meta_table.meta_key='last_name' THEN meta_table.last_name ELSE NULL END) as last_name,
MAX(CASE WHEN meta_table.meta_key='phone_number' THEN meta_table.phone_number ELSE NULL END) as phone_number,
MAX(CASE WHEN meta_table.meta_key='country' THEN meta_table.country ELSE NULL END) as country,
MAX(CASE WHEN meta_table.meta_key='years_of_experience' THEN meta_table.years_of_experience ELSE NULL END) as years_of_experience,
MAX(CASE WHEN meta_table.meta_key='highest_degree_obtained' THEN meta_table.highest_degree_obtained ELSE NULL END) as highest_degree_obtained,
MAX(CASE WHEN meta_table.meta_key='availability_for_work' THEN meta_table.availability_for_work ELSE NULL END) as availability_for_work,
MAX(CASE WHEN meta_table.meta_key='english_proficiency' THEN meta_table.english_proficiency ELSE NULL END) as english_proficiency,
MAX(CASE WHEN meta_table.meta_key='disciplines' THEN meta_table.disciplines ELSE NULL END) as disciplines,
MAX(CASE WHEN meta_table.meta_key='profile_picture' THEN meta_table.profile_picture ELSE NULL END) as profile_picture,
MAX(CASE WHEN meta_table.meta_key='resume' THEN meta_table.resume ELSE NULL END)    as resume,
MAX(CASE WHEN meta_table.meta_key='description' THEN meta_table.description ELSE NULL END) as description,
MAX(CASE WHEN meta_table.meta_key='hourly_rate' THEN meta_table.hourly_rate ELSE NULL END) as hourly_rate,
MAX(CASE WHEN meta_table.meta_key='satisfaction_rating' THEN meta_table.satisfaction_rating ELSE NULL END) as satisfaction_rating,
MAX(CASE WHEN meta_table.meta_key='invited_projects' THEN meta_table.invited_projects ELSE NULL END)  as invited_projects,
MAX(CASE WHEN meta_table.meta_key='completed_project_count' THEN meta_table.completed_project_count ELSE NULL END) as completed_project_count

# The table we're selecting from

from tsd_retro_users as users

# Join in our usermeta table for each individual meta value

inner join tsd_retro_usermeta  as meta_table
ON  users.ID = meta_table.user_id

# Define our select stipulations

where 
(users.ID = 20)
GROUP BY users.ID

一旦我们使用 php，您就可以这样做:

$requiredMetaFields = array(
'first_name',
'last_name',
'phone_number',
'country',
'years_of_experience',
'highest_degree_obtained',
'availability_for_work',
'english_proficiency',
'disciplines',
'profile_picture',
'resume',
'description',
'hourly_rate',
'satisfaction_rating',
'invited_projects',
'completed_project_count'
);

$query = "select
    users.ID,
    users.user_email,
    users.display_name,";

foreach($requiredMetaFields as $metafield) {
$query .= "MAX(CASE WHEN meta_table.meta_key='$metafield' THEN meta_table.$metafield ELSE NULL END) as $metafield, ";
}
$query =substr($query,0,-1);
$query .= "from tsd_retro_users as users
inner join tsd_retro_usermeta  as meta_table
ON  users.ID = meta_table.user_id
where 
(users.ID = 20)
GROUP BY users.ID";

已编辑语法错误