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 14 comments
I'd say you just need:
SELECT b.id, u.name, b.title FROM books b JOIN user u ON b.owner = u.id
Then you can search WHERE u.name='Steve' but it's so obvious for someone mentioning Views that I'm ashamed to say.
Also you can accomplish the numbering with:
SELECT (@i:=@i 1)-1 my_consecutive_numbering, b.* FROM books b WHERE @i:=1
So each row would have a consecutive number (but do NOT order by this alias as it'd result on querying and numbering the whole table!!!). And keep in mind that the @i:=1 on the where clause is just for restarting the variable.
I certainly haven't answered you but anyway... best regards, Diogo