not me… you need a performance expert
Can you please post some sample data for each table?
Hi-i’ve hired a performance specialist to look into this now. I’ll report back when they’ve sorted the query out.
Thanks for all of your help.
Here’s the optimized query. Now, only one final problem. How do I perform a count on the returned rows? Currently, i’m getting back an error (don’t think it likes the temporary tables being created).
create temporary table uo1 (key (user_id)) 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 ;
create temporary table jt1 (key(user_id)) select user_id,
group_concat(j.job_type SEPARATOR ', ' ) as job_type from
users_job_types as jt inner join job_types as j on j.id = jt.job_type
group by user_id;
create temporary table ks1 (key(user_id)) select user_id,
group_concat(k.key_skill SEPARATOR ', ' ) as key_skill from
users_keyskills as ks inner join key_skills as k on k.id =
ks.key_skill group by user_id;
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, jt1.job_type, ks1.key_skill from
users inner join users_profile on users_profile.userid = users.id left
outer join uo1 on uo1.user_id = users.id left outer join jt1 on
jt1.user_id = users.id left outer join ks1 on ks1.user_id = users.id
where users.status='1' group by users.id order by users.signup_date
desc;