Mingo, The actually "counting" is unlikely to be affected, but you are forcing the planner to look up the column names in the sys tables for no good reason - and that initial task while neglible is superflous.
-mark -----Original Message----- From: Mingo Hagen [mailto:[EMAIL PROTECTED] Sent: Friday, March 17, 2006 10:29 AM To: CF-Talk Subject: Re: Large count query > caching Hi Aaron, Are you sure about that? I just tried three different queries (count(*), count(field) and count(0)) on two joined tables with a date selection which return a count of about 500000 records. all three queries run in exactly the same time (1.5sec). I think I read somewhere that it should even be the opposite (although my tests don't show it) something to do with a shortcut to the recordcount of a table mapped to count( * ) or something. Mingo. Wolfe, Aaron wrote: > You should use a field name in the count instead of the *. It should > speed it up. > SELECT COUNT( fieldname ) FROM myTable > With the * it has to return all records and all fields. > > > -----Original Message----- > From: Jeremy Bunton [mailto:[EMAIL PROTECTED] > Sent: Friday, March 17, 2006 9:28 AM > To: CF-Talk > Subject: Large count query > caching > > I have a query counting records in a 25 million plus database. Even with > indexing and what not it takes about 25 seconds to run this query. > (count * from table where date > 19000101) Is there a way I could cache > the query so that if it is ran in that same way it very quickly returns > the full count. > I.E. this is a dynamically build query so sometimes it will have a long > where clause that I may be different than the straight just count > everything thing like above. It seems to me that if that query has been > ran one time in the same way that query should not have to recount the > next time, and should just output the count. > > Jeremy > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235656 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
