asked on

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

brad2575

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)

ASKER CERTIFIED SOLUTION

simonzebu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

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

NerdsOfTech

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