I have a query that is very very slow to the point where it doesn’t even load in phpMyadmin. The problem seems to relate to the users_job_types table (it has 33,000 entries). When I remove the left join on the users_job_types it loads in 0.33 seconds.
Any ideas what may be causing it to be so slow? Any idea how to fix this?
select
users.title,
users.first_name,
users.last_name,
users.gender,
users.city,
users.county,
users.cv_text,
users.signup_date,
users_profile.*,
o.occupation, j.job_type
from users
inner join users_profile on users_profile.userid = users.id
left outer join
( select user_id, group_concat(occupations.occupation SEPARATOR ', ') as occupation from users_occupations left join occupations on occupations.id = users_occupations.occupation group by user_id ) as o on o.user_id = users.id
left outer join
( select user_id, group_concat(users_job_types.job_type SEPARATOR ', ') as job_type from users_job_types left join job_types on job_types.id = users_job_types.job_type group by user_id ) as j on j.user_id = users.id
where users.status='1'
group by users.id
order by users.signup_date desc
select
user_id
, group_concat(users_job_types.job_type SEPARATOR ', ') as job_type
from users_job_types
left join job_types
on job_types.id = users_job_types.job_type
group by user_id
I have a question about this part of your query: why are you (left) joining the job_types table? You aren’t selecting any column from it, are you?
select
users.title,
users.first_name,
users.last_name,
users.gender,
users.city,
users.county,
users.cv_text,
users.signup_date,
users_profile.*,
group_concat(o.occupation SEPARATOR ', ') as occupation,
group_concat(j.job_type SEPARATOR ', ') as job_type
from users
inner join users_profile
on users_profile.userid = users.id
left outer join users_occupations as uo
on uo.user_id = users.id
left join occupations as o
on o.id = uo.occupation
left outer join users_job_types as j
on j.user_id = users.id
where users.status='1'
group by users.id
order by users.signup_date desc
How about this?
And make sure you have all the right indexes.
Thanks guys. I seem to have resolved the slow issue, now the only problem is that the concat is pulling the information multiple times. Below is the query i’m using - any ideas what could be wrong with it?
SELECT users.title, users.first_name, users.last_name, users.gender, users.city, users.county, users.cv_text, users.signup_date, users_profile . * , group_concat( o.occupation
SEPARATOR ', ' ) AS occupation, group_concat( j.job_type
SEPARATOR ', ' ) AS job_type, group_concat( ks.key_skill
SEPARATOR ', ' ) AS key_skill
FROM users
INNER JOIN users_profile ON users_profile.userid = users.id
LEFT OUTER JOIN users_occupations AS uo ON uo.user_id = users.id
LEFT OUTER JOIN users_job_types AS j ON j.user_id = users.id
LEFT OUTER JOIN users_keyskills AS ks ON ks.user_id = users.id
LEFT JOIN occupations AS o ON o.id = uo.occupation
WHERE users.status = '1'
GROUP BY users.id
ORDER BY users.signup_date DESC
the problem is, you are seeing cross join effects for each user
if a given user has 2 occupations, 3 job types, and 4 skills, then your joins are going to produce 24 rows
to get around this problem, do the counting in subqueries…
SELECT users.title
, users.first_name
, users.last_name
, users.gender
, users.city
, users.county
, users.cv_text
, users.signup_date
, users_profile.*
, uo1.occupation
, j1.job_type
, ks1.key_skill
FROM users
INNER
JOIN users_profile
ON users_profile.userid = users.id
LEFT OUTER
JOIN ( SELECT user_id
, GROUP_CONCAT( o.occupation SEPARATOR ', ' ) AS occupation
FROM users_occupations AS uo
INNER
JOIN occupations AS o
ON o.id = uo.occupation
GROUP
BY user_id ) AS uo1
ON uo1.user_id = users.id
LEFT OUTER
JOIN ( SELECT user_id
, GROUP_CONCAT( j.job_type SEPARATOR ', ' ) AS job_type
FROM users_job_types AS j
GROUP
BY user_id ) AS j1
ON j1.user_id = users.id
LEFT OUTER
JOIN ( SELECT user_id
, GROUP_CONCAT( ks.key_skill SEPARATOR ', ' ) AS key_skill
FROM users_keyskills AS ks
GROUP
BY user_id ) AS ks1
ON ks1.user_id = users.id
WHERE users.status = '1'
ORDER
BY users.signup_date DESC
Right, I get it. So it’s getting a row for each occupation, job type and skill and returning them all.
Ok, I just tried that updated query, only problem is…it’s reverted back to it’s sluggish ways and takes ages (im actually still waiting for it to complete the query as I write this post).
Thanks, I tried your suggestion r937 - but the query is still not loading. I have no clue why this is happenning
Do you think I should revert back to the way I had it before - without subquery? It was loading then, but just loading the information multiple times. Or is subquery the only way out?
Thanks so much