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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
simonzebuAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.