« Return to Thread: Limit the results of a COUNT

Limit the results of a COUNT

by donr2020 :: Rate this Message:

Reply to Author | View in Thread

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:

SELECT Main.Col1, NoFilter.TotCount, Filter1.SubCount, etc. FROM
(
SELECT Col1 FROM Table WHERE Col1 = X and Col2 > Y and Col3 < Z LIMIT 1, 30
) Main

INNER JOIN
(
SELECT COUNT(*) AS TotCount FROM Table
) NoFilter

INNER JOIN
(
SELECT COUNT(*) AS SubCount WHERE Col2 > Y
) Filter1

ETC.

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?

Thanks

 « Return to Thread: Limit the results of a COUNT