Count from a table, but stop counting at a certain number

Question

Is there a way in MySQL to COUNT(*) from a table where if the number is greater than x, it will stop counting there? Basically, I only want to know if the number of records returned from a query is more or less than a particular number. If it's more than that number, I don't really care how many rows there are, if it's less, tell me the count.

I've been able to fudge it like this:

-- let x be 100

SELECT COUNT(*) FROM (
    SELECT `id` FROM `myTable`
    WHERE myCriteria = 1
    LIMIT 100
) AS temp

...but I was wondering if there was some handy built-in way to do this?

---

Thanks for the suggestions, but I should have been more clear about the reasons behind this question. It's selecting from a couple of joined tables, each with tens of millions of records. Running COUNT(*) using an indexed criteria still takes about 80 seconds, running one without an index takes about 30 minutes or so. It's more about optimising the query rather than getting the correct output.

Answer 1

SELECT * FROM WhateverTable WHERE WhateverCriteria
LIMIT 100, 1

LIMIT 100, 1 returns 101th record, if there is one, or no record otherwise. You might be able to use the above query as a sub-query in EXIST clauses, if that helps.

Answer 2

I can't think of anything. It looks to me like what you're doing exactly fulfils the purpose, and SQL certainly doesn't seem to go out of its way to make it easy for you to do this more succinctly.

Consider this: What you're trying to do doesn't make sense in a strict context of set arithmetic. Mathematically, the answer is to count everything and then take the MIN() with 100, which is what you're (pragmatically and with good reason) trying to avoid.

Answer 3

This works:

select count(*) from ( select * from  stockinfo s limit 100 ) s

but was not any faster (that I could tell) from just:

select count(*) from stockinfo

which returned 5170965.

Ron