I am using a pagination of <<< 1 | 2 | 3 >>> style in mysql and Oracle. I agree that OFFSET slows down in MySQL after 20,000 records, that's why I suggest my clients to use Oracle after their database grows. If you are interested in my pager code I can post it here, although it might be written better.
On May 16, 10:10 pm, TheDude <officed...@gmail.com> wrote: > Do those examples use the OFFSET paramter? I'm not sure how this > affects Oracle or PGSQL, but in MySQL this could be considered a slow > query after having 20,000+ rows and exponentially grows from there (I > know pgsql has some start-up cost performing this). May want to keep > in mind that a better alternative for pagination would be a system > like... > > Have URL() map out a paramter called 'last_seen' and store the last id > given from the query results (or you can just increment by "X" value > such as 25). Then perform a db(db.table.id > [the parameter]).select > (..., limitby(thelimit)). > > This way, the query performed would look like this: > SELECT [fields] FROM [table] WHERE id > [the parameter] LIMIT BY > [thelimit]; > > If you have deleted entries within the DB it *will* mess up. You have > several options... > - Implement a error margin in the limit by, but during the view set a > hard limit using a counter. So if you want 25 results per page, LIMIT > BY 40 then in your view as your looping through have a variable called > "counter" increment it, once it reaches 25 then break out of the loop. > - Implement a system where it finds the first missing ID and insert > the new entry with that ID (don't recommend). > - An interesting approach would be to have a table with the original > table's name then appending it with a "_missing_ids" So if you have > the table "users" add another table called "users_missing_ids" and > then when you delete an entry from table "users" have the app store > the users ID into the missing table. Then, you can just select from > that table and inject the ID manually with the new insert row. > - Even better, have the DB handle the method above using triggers, > functions, procedures, etc. if the dbms can do that. > > On May 16, 1:15 pm, virhilo <virh...@gmail.com> wrote: > > > maybe look at:http://mdp.cti.depaul.edu/AlterEgo/default/show/63 > > andhttp://mdp.cti.depaul.edu/AlterEgo/default/show/95 > > > On 16 Maj, 18:39, Jason Brower <encomp...@gmail.com> wrote: > > > > It would be nice to have integrated pagination, or perhaps some > > > documentation on how it's done. And both would be a dream. :D > > > I am sure this has been brought up before, but I want to plug it again > > > as I really would like this feature in time for my thesis. ;) > > > Regards, > > > Jason Brower --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py Web Framework" group. To post to this group, send email to web2py@googlegroups.com To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
