I am running Postgre 7.4 on FreeBSD. The main table have 2 million record
(we would like to do at least 10 mil or more). It is mainly a FIFO structure
with maybe 200,000 new records coming in each day that displace the older
records.

        I'm so sorry, but I have to rant XDDD

People who present a list of 100 items, paginated with 10 items per page so that it fits on half a 800x600 screen should be shot.
        I can scroll with my mousewheel and use text search in my browser...

People who present a paginated view with 100.000 pages where you have to apply bisection search by hand to find records starting with "F" are on page 38651 should be forced to use a keyboard with just 1 key and type in morse code.

Problem of pagination is that the page number is meaningless and rather useless to the user. It is also meaningless to the database, which means you have to use slow kludges like count() and limit/offset. And as people insert stuff in the table while you browse, when you hit next page you will see on top, half of what was on the previous page, because it was pushed down by new records. Or you might miss records.

So, rather than using a meaningless "record offset" as a page number, you can use something meaningful, like a date, first letter of a name, region, etc.

Of course, MySQL, always eager to encourage sucky-sucky practices, provides a neat CALC_FOUND_ROWS hack, which, while not being super SQL standard compliant, allows you to retrieve the number of rows the query would have returned if you wouldn't have used limit, so you can compute the number of pages and grab one page with only one query.

So people use paginators instead of intelligent solutions, like xmlhttp+javascript enabled autocompletion in forms, etc. And you have to scroll to page 38651 to find letter "F".

        So if you need to paginate on your site :

        CHEAT !!!!

        Who needs a paginated view with 100.000 pages ?

        - Select min(date) and max(date) from your table
- Present a nifty date selector to choose the records from any day, hour, minute, second
        - show them, with "next day" and "previous day" buttons

- It's more useful to the user (most likely he wants to know what happened on 01/05/2005 rather than view page 2857) - It's faster (no more limit/offset ! just "date BETWEEN a AND b", indexed of course)
        - no more new items pushing old ones to the next page while you browse
        - you can pretend to your boss it's just like a paginated list

        












---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to