Speeding up an old query

Databases
1

Hello!

I got some help writing a query a while back and am revisiting it with the hopes of speeding it up. In the past I used multiple subqueries but now realize that these guys can be super, super slow. Basically, I’d like to get all of the question id’s that fit a certain topic (The Language of Algebra), that are unused by a particular user. The user can either use a question_id as a review question (hence the ungraded_review_questions.user_id<>1 statement below) or the question is in none of their assigned HW’s for a particular course (this is the part I need help with). So far I have:

SELECT questions.question_id
FROM questions
INNER JOIN assignments_topics ON questions.section_name = assignments_topics.section_name
AND questions.question_id
LEFT JOIN ungraded_review_questions ON questions.question_id = ungraded_review_questions.question_id
WHERE assignments_topics.chapter_name =  'The_Language_of_Algebra'
AND ungraded_review_questions.user_id <>  '1'
GROUP BY questions.question_id

(The GROUP BY statement is there because different users could use the same question).

I’d like to add something like:

AND question._id NOT IN
(SELECT question_id FROM assignments_questions INNER JOIN assignments_instructors ON assignments_questions.assignment_id = assignments_instructors.assignment_id WHERE assignments_instructors.course_id = 39)

In other words, the question shouldn’t be in any other HW assignments for a particular course. However, since this is a sub-query, I’m wondering if it might be better/possible to somehow integrate into the query with LEFT JOIN: I’m a little stumped because this portion of the query would include an INNER JOIN as well.

Any thoughts would be appreciated,

Eric

2

you’re probably going to have to spend just a bit more time explaining what the tables are for and how they’re related

in the meantime, i noticed two things wrong with your original query

first is the incomplete condition here –

INNER JOIN assignments_topics 
ON questions.section_name = assignments_topics.section_name
[COLOR="Red"]AND questions.question_id[/COLOR]

second is that fact that you have a non-null condition on a column of the right table in a LEFT OUTER JOIN –

LEFT JOIN ungraded_review_questions ON ...
WHERE ... AND ungraded_review_questions.user_id <>  '1'

this rules out the possibility of the right table ever having nulls, which means the join functions as an inner join, not a left outer join

can you fix those things first, and report on how the query is doing?

3

Thanks for the reply! If this question is too confusing/complicated, please let me know…

I think that what you’re looking for is:

questions: contains information about each question including its unique question id, the question text, solution, and web page that the question relates to (what I call section_name) in a book; there are many questions for a given section_name

assignment_topics: contains information on the different possible chapters. Each web page has a unique name, which I call section_name. The various web pages (section_names) then fall under particular chapters, which I call chapter_name.

ungraded_review_questions: contains all of the question_id’s for a particular user that they can use as ungraded practice questions.

In the query below, then, I’m specifically looking for all of the ungraded practice questions that fall under the chapter The Language of Algebra, not yet used by the user_id with id 1.

With this in mind, I actually think that I do want the <> 1 portion of the query (though please correct me if I’m wrong!). The questions table has all of the questions, my first inner join won’t remove any records since each question has a section_name. Then, the WHERE assignments_topics.chapter_name = ‘The_Language_of_Algebra’ limits the questions to that chapter while the AND ungraded_review_questions.user_id <> ‘1’ axes questions already picked by that particular user (remember, I’m looking for still available questions).

Regarding the random AND questions_id … I put egg on my face for a bad copy/paste job. Sorry!

SELECT questions.question_id
FROM questions
INNER JOIN assignments_topics ON questions.section_name = assignments_topics.section_name
LEFT JOIN ungraded_review_questions ON questions.question_id = ungraded_review_questions.question_id
WHERE assignments_topics.chapter_name =  'The_Language_of_Algebra'
AND ungraded_review_questions.user_id <>  '1'
GROUP BY questions.question_id

As is, the query seems to do what I want it to do when I tested it. The problem, is that there are two places that an instructor can put questions. They can either be ungraded review problems (those were the ones from the query above) or they can they can be used in graded assignments for a particular course. This is where the following comes in:

A

AND question._id NOT IN
(SELECT question_id
FROM assignments_questions
INNER JOIN assignments_instructors
ON assignments_questions.assignment_id = assignments_instructors.assignment_id
WHERE assignments_instructors.course_id = 39)

assignments_questions contains all of the question ids for the variety of assignments along with their assignment ids.
assignments_instructors contains the assignment ids, the names of the assignments, and when they’re due, and which course they fall under.

So for the last piece, I’m also trying to ax out any assigned homework questions from a particular course; in this case course 39, so as to avoid my user (that’s user 1) from using any questions that are already in one of the homework assignments.

So, in summary, I need to get rid of user 1’s ungraded practice review problems (the first successful query) AND I need to get rid of his assigned HW problems – my current issue.

This question may very well go beyond the call of duty…if so, please just let me know, I’ll sit with it for a few weeks and try again with a fresh perspective!

-Eric

4

sorry for the delay, try this –

SELECT questions.question_id
  FROM assignments_topics 
INNER 
  JOIN questions
    ON questions.section_name = assignments_topics.section_name
LEFT OUTER
  JOIN ungraded_review_questions 
    ON ungraded_review_questions.question_id = questions.question_id
   AND ungraded_review_questions.user_id = 1
LEFT OUTER
  JOIN ( SELECT assignments_questions.question_id
           FROM assignments_instructors 
         INNER
           JOIN assignments_questions
             ON assignments_questions.assignment_id = assignments_instructors.assignment_id 
          WHERE assignments_instructors.course_id = 39 ) AS homework
    ON homework.question_id = questions.question_id
 WHERE assignments_topics.chapter_name =  'The_Language_of_Algebra'
   AND ungraded_review_questions.question_id IS NULL
   AND homework.question_id IS NULL
GROUP 
    BY questions.question_id
5

Not to worry! I was able to come up with a combined SQL/php solution for the last piece BUT your solution is way, way clearer. Plus, I’ve been looking around in forums for good examples of 2 particular syntactical skills that I was lacking: using an alias for one of the tables (AS homework in your solution) and using a series of inner and left outer joins in one query. Your solution is the perfect example of both and will be a great model for me to use in the future.

Finally, just to let you know just how much faster your query was than my original query, which contained two subqueries to get rid of the questions that I didn’t want (using AND NOT IN), it went from 21 seconds (embarrassing!!!:blush:) down to .0474 seconds :lol:.

Thanks again and enjoy your weekend.
-Eric

6

thanks :slight_smile: