People ====== id lname fname insid homephone homeemail
Invoice ======= id (in format yyyymmddxxx)
In the people table, the 'insid' field points to another people table entry's 'id' field. In the invoice table, the 'custid' field points to an 'id' field in the people table. The 'id' field of the invoice table is an 11-digit number in the format 'yyyymmddxxx' where xxx is a unique 3-digit number. The date before the 'xxx' is the date the entry was created. The query I'm trying to create finds any people.id that the last invoice.id was more than 120 days ago. I haven't gotten to the date checking yet, but this is what I have. It takes about ~5 minutes to run with 1,500 records in the people table and probably 25,000 records in the invoice table. The box that its running on is pretty beefy: dual Athlon MP 2200+ w/ 768MB DDR. Is there anything I can do to optimize this query?
SELECT people.id, people.lname, people.fname, people.homephone, people.homeemail, instr.lname AS ilname, instr.fname AS ifname, max(invoice.id) AS lastinv FROM people LEFT JOIN people AS instr ON people.insid=instr.id LEFT JOIN invoice ON people.id=invoice.custid GROUP BY people.id
-- Andrew Gaffney
-- [EMAIL PROTECTED] mailing list
