CODEWITHSUNDEEP
mysql
soisystems
soisystems

Reputation: 194

Need help fixing MySql Query

Here is the query I'm trying to run: 

SELECT A.*
FROM student_lesson_progress A  
LEFT JOIN student_lesson_progress B  
ON  A.studentId = B.studentId  
AND A.lessonId = B.lessonId  
WHERE A.lessonStatusTypeId = 2 AND   
EXISTS (SELECT * FROM student_lesson_progress WHERE B.lessonStatusTypeID = 4)

Basically I'm not very skilled with SQL, but am trying to return all rows with a lessonStatusTypeID = 2 but only if there is a row with the same studentId and lessonId that has lessonStatusTypeID = 4.

My end goal once I am certain I have the query right, is that if a Student (studentID) has achieved a Status (lessonStatusTypeId) of 4 on a particular lesson (lessonID) I want to delete all the rows where Status is 2 for that particular Student on that particular lesson, as that data is no longer needed.

I pieced together the above query, and it runs alright on a small test DB, and seems to be returning the desired rows. However, when I try and run it on the production DB, where the student_lesson_progress table has around 600,000 rows, it just runs and runs and runs, locks up the database, pins the server cpu at 100%, and never returns data.

My guess is that my query is very poorly put together, and probably overly complicated for what I'm trying to do. I would greatly appreciate any tips or nudges in the right direction with this one.

Upvotes: 0

Views: 66

Answers (2)

mikeb
mikeb

Reputation: 11267

General rule of thumb: If you're using a sub-select, you're probably not doing it right. This is not always the case, but if you can avoid sub-selects, you should.

This should work for the query. Your sub-select is what is probably killing your performance. You also should index sutdentId and lessonId, or put a compound index on both columns.

SELECT A.*
FROM student_lesson_progress A  
INNER JOIN student_lesson_progress B  
ON  A.studentId = B.studentId  
AND A.lessonId = B.lessonId  
WHERE A.lessonStatusTypeId = 2 AND B.lessonStatusTypeID = 4

Upvotes: 2

K.AJ
K.AJ

Reputation: 1292

You need to use corelated subquery. But first make sure you have right indices on the table.

SELECT distinct A.*
FROM student_lesson_progress A  
WHERE A.lessonStatusTypeId = 2
AND   A.studentId in (
   SELECT B.studentId
   FROM   student_lesson_progress B
   WHERE  B.studentId = A.studentId
   And    B.lessonStatusTypeId = 4);

Essentially means, get me list of all students with status 2, who also have a corresponding lesson with status of 4. The distinct will eliminate duplicates (if a student has more than 1 lesson with status 4).

Hope this works..

Upvotes: 0

Related Questions