Steve Kemp's Blog

Debian & Free Software

About This Site

This is a simple blog relating to Debian & Free Software issues.

Archive
You think we just work at a comic book store for our folks, huh?
19th 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.

Tags: lazyweb, mysql, paging, questions.

Comments On This Entry

you don't need an id to make paging trivial. if you want 20 items at a time, the first 20 is: select * FROM books WHERE owner=73 LIMIT 20 and the next 20 is: select * FROM books WHERE owner=73 LIMIT 20,20 and the next 20 is: select * FROM books WHERE owner=73 LIMIT 40,20
stew Submitted at 14:18:32 on 19 July 2008
I don't understand what you need the id for - if it's just for paging you can use the LIMIT and OFFSET constructs to get out the records you want. That, combined with a join on the user table should be enough to give you what you want, without any views required.
Penny Submitted at 14:29:56 on 19 July 2008

I guess the parts that I'm not explaining are why I need the ID - but I'd forgotten that you limit like that which I guess makes my problem simpler.

Anyway thanks for your comments both of you, and if it doesn't get much better than that it'll have to do - I'll work on the ID part separately.

Steve Kemp Submitted at 14:31:50 on 19 July 2008
ORDER BY id ASC ?
Zerd Submitted at 15:24:50 on 19 July 2008

Zerd - That doesn't work because the IDs in the books table aren't consecutive for the owner. e.g.:

id=1 owner=4 ..
id=2 owner=5 ..
id=3 owner=4 ..

I wanted to specificaly have ascending IDs with no gaps for certain purposes, but adding an ORDER BY clause wouldn't give me that - just that the IDs were ascending/descending.

Steve Kemp Submitted at 15:38:34 on 19 July 2008
Perhaps you could use (owner,id) as the primary key? That way each owner can have his own ID series. But you're going to run into trouble if a book needs to change owner or needs to be removed.
pdr Submitted at 16:14:31 on 19 July 2008
I do not get it. Why do you need a view in the first place?
cstamas Submitted at 17:04:45 on 19 July 2008
Not related to your problem, but please consider using this rule. I learned it's a good habit to call your table to what a single tuple in the table represents. Not `books', but `book'.
Ed Schouten Submitted at 21:10:16 on 19 July 2008
Can you not generate the ids in your code? I would point out that you currently have a one to many relationship with users and books. That is a user can have more than one book, but a book can only have one user. If two users have the same book, then you're duplicating information. If that's a problem, then you should have a mapping table with (id, user_id, book_id).
It might be worth explaining why you need a consecutive id for each user. What happens if they remove a book?
David Pashley Submitted at 22:25:57 on 19 July 2008
Wow your SQL skills are incredibly low!
Flavio Submitted at 22:46:33 on 19 July 2008
I can't get why you'd need consecutive ids either.
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
Diogo Vieira Submitted at 00:37:26 on 20 July 2008
You mean what some databases do with "ROW_NUMBER".
Searching "ROW_NUMBER mysql" gives some ideas.
But row numbering is very "unrelational". Why do you want row numbers?
Simon Submitted at 00:52:11 on 20 July 2008

Thanks for all the feedback.

The reason why I need consecutive IDs in this result is for complicated reasons which I'm not going to go into - but it has nothing to do with users or books. That was a made-up simplified example.


Steve Kemp Submitted at 10:55:36 on 20 July 2008
Again, don't see why you need to do this, or why it can't be done a layer higher, but using a variable (http://dev.mysql.com/doc/refman/5.0/en/user-variables.html) to hold a counter appears to work:
SET @ctr :=0;
SELECT @ctr := @ctr 1, c.* FROM `caption` c WHERE `contributorid` = 178
Sven Edge Submitted at 14:46:12 on 20 July 2008

Tags

Created by Chronicle v3.1