Insight in query optimization
I am trying to find here mainly users who go in for sports and activity-focused regions. There are about 17K users in the acces [users] table. Everyone can have a certain number of sports interests and one region.
In this query, look for every user who has one sport and one region with at least one action target. Sports can be up to 75 when we select each one [not quite good with IN query].
SELECT a.user, pp.courriel
FROM acces a
LEFT JOIN acces_profil_sport ap ON ap.id = a.id
LEFT JOIN profil_perso pp ON pp.id = a.id
WHERE ap.sport_id IN
(
SELECT ac.sport_id
FROM activite_sport ac
RIGHT JOIN activite a ON a.activite_id = ac.activite_id AND a.is_cron = 1 AND a.cron_processed = 0
)
AND pp.region_id IN
(
SELECT ar.region_id
FROM activite_region ar
RIGHT JOIN activite a ON a.activite_id = ar.activite_id AND a.is_cron = 1 AND a.cron_processed = 0
)
GROUP BY a.id
If I remove the sport search, the query takes about 30 seconds to run. Otherwise it will take quite a long time and use about 99% proc with mysql.
Any hints to help this?
[edit: table structure]
Acces: id (primary key), user, perso _
id (key / foreign key for profiling _
perso [perso _
id]) [some-other-fields]
profil _
perso: perso _
id (primary key) courriel, region _
id, id (foreign key to acces [id]) [some other fields]
acces _
profil _
sport: id / sport _
id (double primary key), niveau _
id (double key with sport _
id)
a source to share
I suspect your indexes are wrong. If you print out the explanation of select ... I can better comment on this. Also, I'm curious as to why you are doing left / right joins and subqueries.
It seems to me that they should all be fine as the two left joins will only work if they exist. If they are null, you will not get the row due to the required subquery match.
As far as right side joins go, you want the ar bit that is not part of the right side. I would either remove them or make them connect directly. My guess is that since you are checking what looks like a raw cron job, you want to save them.
SELECT a.user, pp.courriel
FROM acces
JOIN acces_profil_sport ap ON ap.id = a.id
JOIN profil_perso pp ON pp.id = a.id
JOIN activite_sport ac ON ac.sport_id = ap.sport_id
JOIN activite a1 ON a.activite_id = ac.activite_id AND a.is_cron = 1 AND a.cron_processed = 0
JOIN activite_region ar ON ar.region_id = pp.region_id
JOIN activite a2 ON a.activite_id = ar.activite_id AND a.is_cron = 1 AND a.cron_processed = 0
a source to share
SELECT acces.user, courriel
FROM acces
JOIN profil_perso ON acces.id = profil_perso.id
WHERE EXISTS (SELECT 1 FROM acces_profil_sport JOIN activite_sport on acces_profil_sport.sport_id = activite_sport.sport_id JOIN activite ON activite.activite_id = activite_sport.activite_id WHERE is_cron = 1 AND cron_processed = 0 AND acces_profil_sport.id = profil_perso.id)
AND EXISTS (SELECT 1 FROM activite_region JOIN activite ON activite_region.activite_id = activite.activite_id WHERE is_cron = 1 AND cron_processed = 0 AND activite_region.region_id = profil_perso.region_id);
a source to share