Need to simplify mysql query

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
simonzebuAsked:
Who is Participating?
 
simonzebuConnect With a Mentor Author Commented:
Thanks, but that takes no account of the first part of the OR phrase:

((ld.lesson_id = l.id AND ld.pupil_id = 53 AND l.type_id = lt.id AND l.type_id = 1) OR

which is there for when type_id = 1


I have been trying different variations and found to my surprise that making a LEFT JOIN between lessons and lesson_invite_deps speeds the whole query to an acceptable time.

So I don't particularly need the answer to the original question any more. cheers
0
 
brad2575Commented:
you can try a union.

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)


UNION

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


0
 
SharathData EngineerCommented:
if you got the answer, its fine. you can check this query also.
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 l.type_id = lt.id
   AND ((ld.pupil_id = 53 AND l.type_id = 1) 
         OR (lid.group_id = g.id AND gd.group_id = g.id AND gd.pupil_id = 53 AND l.type_id > 1)) 
 ORDER BY  l.lesson_date

Open in new window

0
 
NerdsOfTechTechnology ScientistCommented:
No way this will work... Just for fun...
SELECT DISTINCT l.*, loc.location, log.forename, log.surname, lt.type FROM lesson_invite_deps lid, logins log, lesson_types lt, groups g, groupdeps gd INNER JOIN 
(
 locations loc INNER JOIN 
 (
  lessons l INNER JOIN 
  (
   lessondeps ld 
  ) ON l.id = ld.lesson_id
 ) ON loc.id = l.id 
) ON lid.id = l.id 
WHERE 
(
 (ld.pupil_id = 53 AND l.type_id = 1) OR 
 (lid.group_id = g.id AND gd.group_id = g.id AND gd.pupil_id = 53 AND l.type_id > 1)
) 
ORDER BY  l.lesson_date

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.