« Return to Thread: Limit the results of a COUNT

Re: Limit the results of a COUNT

by donr2020 :: Rate this Message:

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 <donr@twensoft.com> 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/mysql?unsub=lists@nabble.com

« Return to Thread: Limit the results of a COUNT