Count from the table, but stop counting with a specific number

Question

Is there a way in MySQL for COUNT(*) from a table where, if a number is greater than x , it stops counting there? Basically, I just want to know if the number of records returned from the request is greater than a certain number. If it's more than this number, I don't care how many lines there are, if it's less, tell me the score.

I managed to do this as follows:

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

... but I was wondering if there is any convenient way to do this?

---

Thanks for the suggestions, but I would have to more clearly explain the reasons for this issue. He selects from a pair of joined tables, each of which contains tens of millions of records. Executing COUNT(*) using an indexed criterion still takes about 80 seconds, and one without an index takes about 30 minutes or so. This is more of a query optimization than getting the right output.

Answer 1

 SELECT * FROM WhateverTable WHERE WhateverCriteria LIMIT 100, 1 

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

Answer 2

I can’t think of anything. It seems to me that what you do exactly matches the goal, and SQL, of course, does not seem to allow it to make it easier for you to make it more concise.

Consider this: what you are trying to do does not make sense in the strict context of a given arithmetic. Mathematically, the answer is to count everything and then take MIN() with 100, which is what you (pragmatically and with good reason) are trying to avoid.

Answer 3

It works:

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

but was not faster (I could say):

 select count(*) from stockinfo 

who returned 5170965.