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.
Tags: lazyweb, mysql, paging, questions
|
