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.