Index help needed

Forum Moderators: open

Message Too Old, No Replies

Index help needed

on this query

txbakers

4:17 am on Nov 2, 2006 (gmt 0)

SELECT students.rrn,firstn,lastn,grade,points,apoints, allst, anorg,seq,part,inst,assignto,audtype,school,city,schools.district FROM students,schoolinfo,schools,instruments WHERE students.schyear='06' and students.schcode=schools.schcode and schools.schcode= schoolinfo.schcode and students.schyear=schools.schyear and students.teach = schoolinfo.rrn and inst = seq and sje='S' and assignto='B' and district <> 10 order by inst,points desc, apoints desc, lastn,firstn

I have an index setup on the "students" table for inst and points.

But the query still processes slowly. Any ideas for further indexing?

Thanks.

FalseDawn

5:45 am on Nov 2, 2006 (gmt 0)

The instruments table does not appear to be participating in any joins - is this intentional?
Otherwise, I'd try indexing all columns that are used in the joins.

txbakers

12:16 pm on Nov 2, 2006 (gmt 0)

Thanks, I'll give that a try. The instruments join is in the "inst = seq" join. Getting the name from the code number.

I'll try to make indecies on the join fields and see what happens.

FalseDawn

5:52 pm on Nov 2, 2006 (gmt 0)

Oh, ok - the SQL is a bit sloppy in that case - you are using tablenames in some joins and not in others. I'd try to rewrite it using ANSI join syntax. :)

[edited by: FalseDawn at 5:53 pm (utc) on Nov. 2, 2006]

syber

4:43 pm on Nov 3, 2006 (gmt 0)

It looks like the reason the query is running so slow is the instruments table is not joined to anything - resulting in a cartisian product (every row in instruments gets joined to every row in the other tables). An index won't help you in this situation. You can avoid this problem in the future by always using the ANSI join syntax.


SELECT students.rrn,firstn,lastn,grade,points,apoints, allst, 
 anorg,seq,part,inst,assignto,audtype,school,city,schools.district  
FROM students JOIN schools 
 ON students.schcode=schools.schode 
 JOIN schoolinfo 
 ON schools.schcode = schoolinfo.schcode AND 
  students.teach = schoolinfo.rrn  
 JOIN instruments 
 ON <need a link here between instruments and another table's column> 
WHERE students.schyear='06' AND inst = seq AND sje='S' AND  
 assignto='B' and district <> 10  
ORDER by inst,points desc, apoints desc, lastn, firstn

FalseDawn

5:55 pm on Nov 3, 2006 (gmt 0)

Read all the posts. :-)