MySQL efficient test if count w/ where is greater than a value

Question

Is there a way to optimize the following query?

SELECT count(*)>1000 FROM table_with_lot_of_rows WHERE condition_on_index;

Using this query, MySQL first performs the count(*) and then the comparison. This is is fast when only few rows satisfy the condition, but can take forever if a lot of rows satisfy it. Is there a way to stop counting as soon as 1000 items are found, instead of going through all the results?

In particular, I'm interested in MyISAM table with full-text condition, but any answer for InnoDB and/or basic WHERE clause will help.

Answer 1

SELECT 1
    FROM table_with_lot_of_rows
    WHERE condition_on_index
    LIMIT 1000, 1;

Works this way:

1. Using the index (which is presumably faster than using the data)
2. Skip over 1000 rows, collecting nothing. (This is better than other answers.)
3. If you make it this far, fetch 1 row, containing only the literal 1 (in the SELECT).

Now you either have an empty result set (<= 1000 rows) or a row of 1 (at least 1001 rows).

Then, depending on your application language, it is easy to distinguish between the two cases.

Another note: If this is to be a subquery in a bigger query, then do

EXISTS ( SELECT 1
    FROM table_with_lot_of_rows
    WHERE condition_on_index
    LIMIT 1000, 1 )

Which returns TRUE/FALSE (which are synonymous with 1 or 0).

Face it, scanning 1001 rows, even of the index, will take some time. I think my formulation is the fastest possible.

Other things to check: Is this InnoDB? Does EXPLAIN say "Using index"? How much RAM? What is the setting of innodb_buffer_pool_size?

Note that InnoDB now has FULLTEXT, so there is no reason to stick with MyISAM.

If you are using MyISAM and the WHERE is MATCH..., then most of what I said is likely not to be applicable. FULLTEXT probably fetches all results before giving the rest of the engine to chance to do these games with ORDER BY and LIMIT.

Please show us the actual query, its EXPLAIN, and SHOW CREATE TABLE. And what is the real goal? To see if a query will deliver "too many" results?

Possible improvement (depending on context)

Since my initial SELECT returns scalar 1 or NULL, it can be used in any boolean context such as WHERE. 1 is TRUE, NULL will be treated as FALSE. Hence EXISTS is probably redundant.

Also, 1/NULL can be turned into 1/0 thus. Note: the extra parens are required.

IFNULL( ( SELECT ... LIMIT 1000,1 ), 0)