query optimisation

[Daniel Kirk]

Hi There,

I was wondering if someone can help me optimize a query.

SELECT m.* FROM Matches m, TippingCompetitions c WHERE m.competitionname = c.name AND (now() + INTERVAL 10 DAY > dateplayed OR round = c.nextround ) AND dateplayed > now() - interval c.matchtime MINUTE ORDER BY competitionname, dateplayed, division;

The query is looking for information about sports fixtures (the Matches table) that are upcoming; upcoming being defined as being played in the next 10 days, or being in the next round of matches to be played in the competition. We also don't want matches that have elapsed (ie where dateplayed > now() - interval c.matchtime minute).


This is what shows up in the slow query log:

# Time: 100210 11:16:35
# User@Host: kirkyonline[kirkyonline] @ toptipper [192.168.1.4]
# Query_time: 23 Lock_time: 0 Rows_sent: 1739 Rows_examined: 575482
SET timestamp=1265800595;
SELECT m.* FROM Matches m, TippingCompetitions c WHERE m.competitionname = c.name AND (now() + INTERVAL 10 DAY > dateplayed OR round = c.nextround ) AND dateplayed > now() - interval c.matchtime MINUTE ORDER BY competitionname, dateplayed, division;

The number of rows examined is roughly the number of entries in the Matches table.

And an explanation:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE c ALL tc_name,tc_nm_nr null null null 2710 Using temporary; Using filesort
1 SIMPLE m ref Matches_cname,Matches_cname_round,Matches_cname_dateplayed,MT_dp_pld_rd_cname,Matches_dp_mn Matches_cname 53 kirkyonline.c.name 169 Using where

Any suggestions on how to reduce the number of rows that the query looks at?

thanks