Sorry, I didn't type the subqueries quite correctly. They all have the same WHERE part (in this case, "WHERE Col1 = X" that essentially "joins" all the queries.
There are six counts that we need and we first tested it as seven separate queries; but that took about 20% longer than one nested set of queries, as there is a little overhead for each query. Your suggestion does help somewhat. Changing the subqueries to a count of limited subqueries reduced a large sample query from 9 seconds down to 5 seconds. We need to get this down some more to about 1 or 2 seconds (or less if possible). We're going to try using VIEW's to see if that helps. Any other thoughts would be appreciated. Best, Don Perrin Harkins wrote: > > On Dec 30, 2007 1:50 PM, donr2020 <[EMAIL PROTECTED]> wrote: >> Our search engine does a master query INNER JOINed to a series of COUNT >> (*) >> subqueries that return what the number of results would have been had the >> user chosen different "filters" (or no filter at all). As an example: > > Hmm. Why are you joining these? There's nothing to join. It looks > like these should be separate queries. > >> This query is being run against a database that currently as 100 Million >> records (and rapidly growing), and if TotCount is over about 50,000, the >> query is unacceptably slow. We need to LIMIT the subqueries to some >> maximum >> count (stop counting at, say, 50,000). Does anyone know a way to do this? > > You can use a temp table, view, or subquery to do it. For example: > > SELECT COUNT(*) FROM > (SELECT id FROM table LIMIT 50000) AS limited_table > > I'm not sure this will actually be faster though. > > - Perrin > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- View this message in context: http://www.nabble.com/Limit-the-results-of-a-COUNT-tp14549988p14561532.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
