Hacker News Reader

>Sure, SQL LIMIT queries and all that, but it’d tend to be nicer for the user.

You'd need to keep a count to do that and a lot of tables you'd bother to paginate, a COUNT() is way too expensive.

Okay so lets say you keep a cache of the COUNT().

What if you're off? Now you have confusing, irregular behavior that throws the user off.

Is this something you've tried in an actual application before?

So SELECT yada yada LIMIT n + epsilon + 1, and if you get that many elements out, display n, otherwise, display all of them.

Count on an indexed column is a pretty fast operation and it's always correct.

count(*) is a linear time operation (roughly to the size of the table) on postgres. It does an index-only scan but with a sufficiently large table/index it's still a problem. It's to do with postgres MVCC implementation. I don't know if other SQL databases have this problem. MySQL doesn't seem to.

It's a problem in mysql too, unless you use an engine like myisam, which isn't realistic in most scenarios due to durability and concurrency concerns.

Reply on news.ycombinator.com