MySQL Forums
Forum List  »  General

Re: Query takes time to execute
Posted by: Harrison Fisk
Date: October 07, 2004 08:48AM

Hi,

You haven't give enough information to give a 100% accurate result. The structure of the table and an EXPLAIN done on the query would help a lot.

The only thing I see that I think might be able to help would be an INDEX on the date column. How selective is that lookup?

For example, what is the difference between:
> SELECT count(*) FROM game_result;
> SELECT count(*) FROM game_result WHERE date >= SUBDATE(NOW(), INTERVAL 1 DAY);

Even with the index, if there are quite a few results from the second one, then it will still be slow. If you have 300,000 items left, that is 300,000 items mysql will need to SUM up, and then sort by, which can take a bit of time unfortunately.

This would be an excellent canidate for a cache of some sort, either on the client side, or on the MySQL side. By that I mean rather than running this query live all of the time, store the results somewhere and then refresh the results every 15 minutes or the like. That will releave a lot of the stress from doing this query, which will still be quite slow for a webpage, even if it took 5-10 seconds instead.

Hope that helps some,

Harrison

Options: ReplyQuote


Subject
Written By
Posted
October 07, 2004 03:15AM
October 08, 2004 01:17AM
Re: Query takes time to execute
October 07, 2004 08:48AM
October 08, 2004 02:59AM


Sorry, you can't reply to this topic. It has been closed.

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.