Entries tagged paging

Related tags: lazyweb, mysql, questions, random, rants.

You think we just work at a comic book store for our folks, huh?

Saturday, 19 July 2008

I'm only a minimal MySQL user, but I've got a problem with a large table full of data and I'm hoping for tips on how to improve it.

Right now I have a table which looks like this:

CREATE TABLE `books` (
  `id` int(11) NOT NULL auto_increment,
  `owner` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  ....
  PRIMARY KEY  (`id`),
  KEY( `owner`)
)  ;

This allows me to lookup all the BOOKS a USER has - because the user table has an ID and the books table has an owner attribute.

However I've got hundreds of users, and thousands of books. So I'm thinking I want to be able to find the list of books a user has.

Initially I thought I could use a view:

CREATE VIEW view_steve  AS select * FROM books WHERE owner=73

But that suffers from a problem - the table has discountinuous IDs coming from the books table, and I'd love to be able to work with them in steps of 1. (Also having to create a view for each user is an overhead I could live without. Perhaps some stored procedure magic is what I need?)

Is there a simple way that I can create a view/subtable which would allow me to return something like:

|id|book_id|owner | title      |....|
|0 | 17    | Steve| Pies       | ..|
|1 | 32    | Steve| Fly Fishing| ..|
|2 | 21    | Steve| Smiles     | ..|
|3 | 24    | Steve| Debian     | ..|

Where the "id" is a consecutive, incrementing number, such that "paging" becomes trivial?

ObQuote: The Lost Boys

Update: without going into details the requirement for known, static, and ideally consecutive identifiers is related to doing correct paging.

| 14 comments.

 

I wish I could tie you up in chains

Sunday, 10 February 2008

Today I've been mostly unwell. Although I have managed to write some minor new code, and watch a little bit of Doctor Who on DVD.

Recently several people have been ranting about Ruby on Rails. I like it, but I wouldn't use it for personal development in a hurry. Deployment is fiddly, and upgrades are annoying.

But one thing that I utterly condemn Rails for is helping to spread bad paging throughout the online world.

So, what is "bad paging" and why is it important? Well cool URLs don't change, right? "Bad paging" is any user-interface which presents you with a limited view upon a changing list of items which is non-bookmarkable.

Consider the following "list". Assume it represents your view of a collection of items numbering 100+. You may only view ten items at a time; clicking "next", or "previous", to navigate your viewport:

1.  first item
2.  second item
..
10. tenth item

[see next: /start/1] [see prev]

Whats wrong with this picture? It is subtle, but this list is broken. The issue is that when the list grows new items are prepended to the front, yet the navigation is linked to the starting page number.

If that description wasn't clear consider what happens if you want to bookmark the page containing item 11. How can you?

Right now it is at /start/1. If a ten new items are appended to the head of the list then it will instead become /start/2 - as items that are currently numbered 1-10 will be shifted forward to become items 11-20, and and they will be on page /start/1 instead.

The solution is simple enough once you consider what you want to happen:

  • Either append items to the end of the list.
    • Such that /start/1 always gives the items 11-20.
  • Number the links in the reverse order.

So why does nobody do that? (As a counter example look at my website: Rather than the 'Show previous' items linking you to the changing link /start/1, it instead links you to /start/569 (for example).

| 2 comments.

 

Recent Posts

Recent Tags