Hi, Bear in mind that your query will include that given month across multiple years. For example, with month = 5, you would be including data from May of 2001, 2002, 2003, 2004, etc. Is that your intention?
Assuming you have an index on due_date, your database will not be able to take advantage of it as your query is currently written. You could create a functional index on month(due_date), but since that maps to only 12 possible values, the benefit would not be large, especially for a massive table. You could try, as an example: "where due_date between '2007-05-01' and '2007-06-01'", which should use the index as well as give you the desired result. If you need to run the query frequently, then you could run it every month and store the results in a separate table for immediate lookup. Maybe you should think about creating a data warehouse at some point. I'm sure the clever people on this list will find other solutions as well. Good luck, Rob On 01 15, 08, at 12:59 PM, joebert jacaba wrote: > I was thinking I may need this query in the future. What I do now is > use paging and only fetch 10 rows. How can you speed up this type of > query? > > select count(*) from table where month(due_date)=month(now()); > _________________________________________________ > Philippine Linux Users' Group (PLUG) Mailing List > [email protected] (#PLUG @ irc.free.net.ph) > Read the Guidelines: http://linux.org.ph/lists > Searchable Archives: http://archives.free.net.ph > _________________________________________________ Philippine Linux Users' Group (PLUG) Mailing List [email protected] (#PLUG @ irc.free.net.ph) Read the Guidelines: http://linux.org.ph/lists Searchable Archives: http://archives.free.net.ph
