Such bounty of comments! Thank you, Michael Stassen.
I see how my logic was faulty, and that a more correct solution would indeed be faster. Thanks for pointing that out and not making me feel like too much of an idiot :) I'll try the revised solution. I am clueful about "join" making more rows/record, but didn't realize that it would be *that* dramatic.
As for the NumericGrade field, I'm basically getting the text grade (e.g. 8th) from someone's MS Access CSV export; I figured I'd leave it in place, and use the numeric grade for sorting. I'd never run into speed issues before, so I was just trying to save myself coding time by sticking with what I had already. I wouldn't need the special lookup tables for grade and subject; if I were to use numeric fields, I could just do a lookup in Perl. Next time, when I try to do this better from the start, I probably will. At the moment, though, I'm trying to avoid changing the Perl code as much as I can (it's much recycled from an earlier project).
Will look into indexing - that's probably covered in my old O'Reilly MySQL/mSQL book.
I am curious about how much faster numeric field comparisons would be to string field comparisons for the Grade field; Would it make enough of a difference to this problem for me to go mucking with this Perl code? This is a CGI Web app, so it's not lightning fast anyway, but then there are a lot of comparisons going on. Currently it looks like it will have about 300 resource records and about six thousand resource_goal records; I should test this myself...
I do have a lot of multi-subject and multi-grade resources; my testing was just not very good, I think.
Oh, and I think I see the error of my ways with regard to my TINYTEXT fields. Probably would do well to shrink those. Easy to change, too.
Thanks! You've given excellent explanations here. I feel like I should buy your book now, if you have one!
I'm wrestling with CSS issues on IE 4.0 for the Mac at the moment, but will return to SQL issues soon, I hope.
More later probably, AM
On Mon, 21 Feb 2005 14:30:59 -0500, Michael Stassen <[EMAIL PROTECTED]> wrote:
As I understand it, you are looking for a resource for 4th grade Social Studies which meets goals 1 through 4. In terms of your tables, that corresponds to having 4 rows in resources_goals, *all* of which have grade=4th and Subject='Social_Studies'. That is, we need to look in *4* copies of resources_goals (not 5). For a given resource id, we want exactly one row from each copy, namely, the row with the correct resource id, correct subject, correct grade, and desired goal number. I think this should do:
SELECT r.id FROM resources as r JOIN resource_goals as g1 ON r.id = g1.ResourceID AND g1.Subject = 'Social_Studies' AND g1.Grade = '4th' AND g1.GoalNumber = 1 JOIN resource_goals as g2 ON r.id = g2.ResourceID AND g2.Subject = 'Social_Studies' AND g2.Grade = '4th' AND g2.GoalNumber = 2 JOIN resource_goals as g3 ON r.id = g3.ResourceID AND g3.Subject = 'Social_Studies' AND g3.Grade = '4th' AND g3.GoalNumber = 3 JOIN resource_goals as g4 ON r.id = g4.ResourceID AND g4.Subject = 'Social_Studies' AND g4.Grade = '4th' AND g4.GoalNumber = 4;
-- Virtue of the Small / (919) 929-8687
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
