Need to simplify mysql query
Posted on 2008-11-18
I am finding all the lessons relevant to a particular pupil. I need to find individual booked lessons and group classes hence the lessondeps for the the former and lesson_invite_deps + groupdeps for the latter...
SELECT DISTINCT l.*, loc.location, log.forename, log.surname, lt.type FROM locations loc, lessons l, lessondeps ld, logins log, lesson_types lt, groups g, groupdeps gd, lesson_invite_deps lid WHERE
loc.id = l.location_id AND log.id = l.prof_id AND
((ld.lesson_id = l.id AND ld.pupil_id = 53 AND l.type_id = lt.id AND l.type_id = 1) OR
(l.id = lid.lesson_id AND lid.group_id = g.id AND gd.group_id = g.id AND gd.pupil_id = 53 AND l.type_id > 1 AND l.type_id = lt.id))
ORDER BY l.lesson_date
The whole query takes about 10 seconds in a web page and several minutes in phpmyadmin. If I take out either half of the OR statements the query runs quickly.
I want to end up with a single record set combining all a pupil's lessons so that they can easily sort by date, prof, location etc.
However the speed of query is unacceptable. Is there some other way I can phrase it?
cheers Simon B