From: Perrin Harkins (perrinelem.com)
Date: Mon Dec 31 2007 - 12:47:02 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

On Dec 30, 2007 1:50 PM, donr2020 <donrtwensoft.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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]