Limit the results of a COUNT
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