CODEWITHSUNDEEP
mysql
user706087
user706087

Reputation: 359

How to count up to certain number of rows e.g. if exceeds 1,000, then select count only up to 1,000?

I know how to use SQL_CALC_FOUND_ROWS with Limit to return total number of rows and results for pagination.

But my question is...

What if (for performance purpose) I want to show if total rows exceeds 1,000 I just want to return 1,000 (even if there are more records)?

SELECT Count(id) FROM table WHERE 1

I Google'd a lot but couldn't find this. I thought of this thinking this may improve Fulltext search speed for example.

thanks in advance.

Upvotes: 2

Views: 2450

Answers (3)

Anthony Pease
Anthony Pease

Reputation: 21

Im pretty sure that mysql has its own counter for the rows built in, when a record gets inputed the marker goes up a notch, what you can do is something like this :

$query = "SELECT * FROM table WHERE maxrows = '1'";

Then have one thousand rows that get the value of one in a maxrows cell, value of two for another one thousand rows and so on

Upvotes: 1

Ed Marty
Ed Marty

Reputation: 39690

SELECT LEAST(Count(id),1000) FROM table WHERE 1

Documentation on the LEAST function can be found here: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_least

Upvotes: 1

Karolis
Karolis

Reputation: 9562

In many cases it can be faster, but you should test it:

SELECT count(*) FROM ( SELECT 1 FROM t LIMIT 1000 ) a

Upvotes: 4

Related Questions