> What is the best choice for my index on this query? > > SELECT id_team, > sum(IF(m.id_visitor = t.id_team,m.visitor_score, > m.home_score)) AS But_pour, > sum(IF(m.id_visitor != t.id_team,m.visitor_score, > m.home_score)) AS But_contre > FROM tab_teams t, tab_matchs m > WHERE t.id_level =4 > AND (m.id_visitor = t.id_team OR m.id_home = t.id_team) AND > m.season = 2 > GROUP BY id_team; >
There's not a whole lot you can do about that query, it's not easy on the database. You can have an index on t.id_level and m.season, but I'm not sure that having one on t.id_team, m.id_visitor, and m.id_home will help, because of the OR in your joining clause. Maybe the best thing to do is cache the results somewhere else :) Perhaps in tab_teams, keep a running total of points for and points against, and update it every once in a while. That way you won't have to run this query too often. --------------------------------------------------------------------- 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
