SQL inner join performance issues

问题:

I have a WordPress installation and I'm making a custom plugin. My plugin stores a good bit of usermeta. I'm using an inner join to combine data from the "users" and "usermeta" table at which point I spit the result back to my PHP script. Here's what my query looks like:

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

As you can see, I inner join the usermeta table for each value that I'm trying to obtain from the database. It all seems to work fine, but after a certain number of inner joins, the query seems to slow to a crawl. Right now, the above query is taking about a second on average, and I only have about twenty users in my user table.

My question is: what are the performance ramifications of inner joins? Is there a better way to run the above query and achieve the same result?

回答1:

Try this way:

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

And as soon as we use php you can do this way:

$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";

Edited syntax errors