[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need to simplify mysql query

Posted on 2008-11-18
4
Medium Priority
?
256 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:simonzebu
4 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 22986809
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
 

Accepted Solution

by:
simonzebu earned 0 total points
ID: 22987210
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
 
LVL 41

Expert Comment

by:Sharath
ID: 22987766
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
 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 22992336
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
In this article, we’ll look at how to deploy ProxySQL.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month19 days, 21 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question