Subject RE: [firebird-support] Re: Sloooow query
Author Dmitry Yemanov

> /* select students who select School A and the priority is 2
> or below */
> SELECT s.student_id, s.priority, s.is_selected, b.grade, b.birth_date
> FROM student_selection s, student_biodata b
> WHERE s.student_id=b.student_id
> AND s.priority<=2
> AND'School A'
> /* exclude students who already accepted in another school */
> AND s.student_id NOT IN
> (SELECT student_id FROM student_selection
> WHERE is_selected='T'
> AND priority<2
> AND school<>'School A')
> /* sort grade from highest to lowest to make sure */
> /* the school get first highest grade of the students */
> ORDER BY b.grade DESC, b.birth_date;

Which indices do you have?

> Any idea to make the query run faster?

If you have an index for "is_selected", try to drop (or deactivate) it. In
some cases it may give you very significant performance boost. Another
option is getting rid of the IN-subquery which is really slow in all IB/FB