What is the fastest way to count the number of MySql rows left after a limited results query-CodePudding

If I have a mysql limited query:

SELECT * FROM my_table WHERE date > '2020-12-12' LIMIT 1,16;

Is there a faster way to check and see how many results are left after my limit?

I was trying to do a count with limit, but that wasn't working, i.e.

SELECT count(ID) AS count FROM my_table WHERE date > '2020-12-12' LIMIT 16,32;

The ultimate goal here is just to determine if there ARE any other rows to be had beyond the current result set, so if there is another faster way to do this that would be fine too.

CodePudding user response：

It's best to do this by counting the rows:

SELECT count(*) AS count FROM my_table WHERE date > '2020-12-12'

That tells you how many total rows match the condition. Then you can compare that to the size of the result you got with your query using LIMIT. It's just arithmetic.

Past versions of MySQL had a function FOUND_ROWS() which would report how many rows would have matched if you didn't use LIMIT. But it turns out this had worse performance than running two queries, one to count rows and one to do your limit. So they deprecated this feature.

For details read:

CodePudding user response：

(You probably want OFFSET 0, not 1.)

It's simple to test whether there ARE more rows. Assuming you want 16 rows, use 1 more:

SELECT ... WHERE ... ORDER BY ... LIMIT 0,17

Then programmatically see whether it returned only 16 rows (no more available) or 17 (there ARE more).

Because it is piggybacking on the fetch you are already doing and not doing much extra work, it is very efficient.

The second 'page' would use LIMIT 16, 17; 3rd: LIMIT 32,17, etc. Each time, you are potentially getting and tossing an extra row.

I discuss this and other tricks where I point out the evils of OFFSET: Pagination

COUNT(x) checks x for being NOT NULL. This is [usually] unnecessary. The pattern COUNT(*) (or COUNT(1)) simply counts rows; the * or 1 has no significance.

SELECT COUNT(*) FROM t is not free. It will actually do a full index scan, which is slow for a large table. WHERE and ORDER BY are likely to add to that slowness. LIMIT is useless since the result is always 1 row. (That is, the LIMIT is applied to the result, not to the counting.)