Take the 2-minute tour ×

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 `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.

share|improve this question
I am afraid that in this case checking for whether COUNT( ) > 100 or COUNT( ) > 1000 or COUNT( ) > 10000 will not make any difference. –  Salman A Nov 19 '09 at 6:33
yes I know, that's why I'm doing the sub query with LIMIT, I was wondering if there was a better way –  nickf Nov 19 '09 at 6:38
i deleted my answer when i saw your edit/intention. i guess your answer in your question is the most optimal one –  Michael Buen Nov 19 '09 at 7:00
add comment

3 Answers

up vote 3 down vote accepted
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.

share|improve this answer
That doesn't look right to me. Won't that include only IDs occurring more than 100 times? If each ID is unique, good luck with that! –  Carl Smotricz Nov 19 '09 at 6:19
I've edited the post. "HAVING" is used in conjunction with "GROUP BY" and "GROUP BY" is not required in this case. –  Salman A Nov 19 '09 at 6:24
No. Now the SQL is syntactically wrong. –  Carl Smotricz Nov 19 '09 at 6:27
Thanks Salman -- I've updated my questions to better explain my situation. –  nickf Nov 19 '09 at 6:29
that's brilliant! –  nickf Nov 19 '09 at 7:30
show 1 more comment

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.

share|improve this answer
add comment

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.

share|improve this answer
From the manual: COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. This optimization applies only to MyISAM tables only, because an exact row count is stored for this storage engine and can be accessed very quickly. –  nickf Nov 19 '09 at 6:48
add comment

Your Answer


