Here is the query that I had in a PhP script that joins a bunch of tables (I'll try to make this look readable). If you're curious, it's for a motorcross site (www.springcreekmx.com):
SELECT standings.rank, race.race_date, class.class_name, racer.racer_name, cycle.cycle_desc, race_event.racer_id, race_event.points_earned, race_event.racing_number, race_event.overall_rank, race_event.round, standings.total_points FROM race_event INNER JOIN racer ON racer.racer_id INNER JOIN standings ON standings.racer_id INNER JOIN race ON race.round INNER JOIN class ON class.class_id INNER JOIN cycle ON cycle.cycle_id WHERE race_event.racing_cycle = cycle.cycle_id AND race_event.class_id = class.class_id AND race.round = race_event.round AND race.series_id = race_event.series_id AND race.class_id = race_event.class_id AND standings.racer_id = racer.racer_id AND standings.class_id = race_event.class_id AND standings.series_id = race_event.series_id AND race_event.racer_id = racer.racer_id AND race_event.class_id = '2' ORDER BY rank, race_date; This syntax actually works, from the posts it sounds like I might be getting away with one. So, from the posts regarding table joins, this would be correctly written as: SELECT standings.rank, race.race_date, class.class_name, racer.racer_name, cycle.cycle_desc, race_event.racer_id, race_event.points_earned, race_event.racing_number, race_event.overall_rank, race_event.round, standings.total_points FROM race_event INNER JOIN racer ON racer.racer_id = race_event.racer_id INNER JOIN standings ON standings.racer_id = racer.racer_id AND standings.class_id = race_event.class_id AND standings.series_id = race_event.series_id INNER JOIN race ON race.round = race_event.round AND race.series_id = race_event.series_id AND race.class_id = race_event.class_id INNER JOIN class ON class.class_id = race_event.class_id INNER JOIN cycle ON cycle.cycle_id = race_event.racing_cycle WHERE race_event.class_id = '$class_search' ORDER BY rank, race_date; Looks much nicer, doesn't seem to be a dramatic increase in performance, but I think it makes more sense this way. Does this look ok? The size of tables (smallest to largest) is series, cycle, class, race, racer, standings, race_event. I re-ordered the INNER JOINS to join on the smallest table like so: SELECT standings.rank, race.race_date, class.class_name, racer.racer_name, cycle.cycle_desc, race_event.racer_id, race_event.points_earned, race_event.racing_number, race_event.overall_rank, race_event.round, standings.total_points FROM series INNER JOIN cycle ON cycle.cycle_id = race_event.racing_cycle INNER JOIN class ON class.class_id = race_event.class_id INNER JOIN race ON race.round = race_event.round AND race.series_id = race_event.series_id AND race.class_id = race_event.class_id INNER JOIN racer ON racer.racer_id = race_event.racer_id INNER JOIN standings ON standings.racer_id = racer.racer_id AND standings.class_id = race_event.class_id AND standings.series_id = race_event.series_id INNER JOIN race_event ON series.series_id = race_event.series_id WHERE race_event.class_id = '$class_search' ORDER BY rank, race_date The first query averages about 0.085 seconds from the mysql prompt, the second about 0.075 seconds and the 3rd 0.065 seconds. Thanks for some great advice, this has been bugging me for a while! Josh --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
