Mysql upper limit for count (*)

Question

I have a request:

select count(*) from `table` where `something`>123 

If the table contains several million records, the query is very slow, although there is an index in the something column. However, in fact, I'm interested in the meaning:

 min(100000, count(*)) 

So, is there a way to prevent MySQL row counting when it already found 100k? I found something like:

 select count(*) from (select 1 from `table` where `something`>123 limit 100000) as `asd` 

This is much faster than count(*) if the table contains several million matching records, but count(*) is much faster when there are less than 100,000 matches.

Is there any way to make this faster?

Answer 1

I have no comments, so I am posting this as an answer ...

• Have you tried using EXPLAIN to see if your index is actually used on something ? This query appears to be performing a table scan . Ideally, you'll want to see something like Extra : Using where: using an index. "
• Out of curiosity, something field with a null value?

Aside, perhaps the query optimizer did a better job of the following:

 select count(*) as cnt from table where something > 123 having count(*) > 100000 

Answer 2

It may help to better use a range limitation.

 select count(*) - (select count(*) from t where something <= 123) as cnt from t 

Another point might be to count the update trigger.