MySQL Forums
Forum List  »  Optimizer & Parser

query optimisation
Posted by: Daniel Kirk
Date: February 10, 2010 08:08AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
query optimisation
2264
February 10, 2010 08:08AM
1447
February 11, 2010 10:46PM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.