5

I have a table Room with primkey on column room_id and another table Items with primkey item_id. Each Item has a foreign integer key to room_id. When my program runs, it only looks at items in a single room so it would help if my primary key was on (room_id, item_id) in this order so that data is sorted on disk in this order.

Is this possible? Or do I need to duplicate room_id in Item and not have it as foreign key?

7
  • Why not just build a nonclustered index on room_id (that will contains item_id as this is the pk) ? Jul 29 '20 at 13:27
  • @DominiqueBoucher Because clustering is exactly what I want. I want my items clustered based on room_id
    – s5s
    Jul 29 '20 at 13:29
  • I'm not sure I get your point... The Clustered index is usually on the primary key (even if it's not required). You can put your clustered index on room_id and item_id but if ever you join you item table to another table, full table scan will be required and this may affect performance. Having a nonclustered index that only contains 2 integer field will not required a lot of extra space and will probably give you the performance improvment you are looking for. When I asked "why not a nonclustered index", I was expecting some technical reasons why you prefer to have them in the clustered index Jul 29 '20 at 13:36
  • Why did you tag your post with both sql server and postgresql? Jul 29 '20 at 13:37
  • @DominiqueBoucher My code accesses items in a single room. If I cluster based on (room_id, item_id) then all item_ids for the same room will be located in adjacent disk locations so seek times will be minimal.
    – s5s
    Jul 29 '20 at 13:39
1

Your clustered index almost certainly should include RoomId for the reasons you correctly identify: it provides an efficient access path to all records for a given Room.

But there are other motivations are well. If Item is a child of Room, then you probably have an identifying relationship like Room contains zero to many items.

This would naturally lead your primary key to be something like (RoomId,<columns that determine a unique item>). So that could be (RoomId,ItemId)assuming that ItemId has some value beyond being a row pointer.

Otherwise it could be (RoomId,SerialNumber) or (RoomId,ItemType) - the exact answer depends on the data you're trying to store.

0

Do Items move around or stay in the same Room forever?

If they move around and [room_id] is the first key column on your clustered index then you're going to end up with a high amount of physical fragmentation and maintenance overhead.

I'd recomend just Clustering on item_id and using room_id on a nonclustered index, perhaps with covering columns to satisfy your application's needs.

How big are the rooms? are we talking end scene of Raiders of the Lost Ark? If the rooms don't hold a lot of items then a Key Lookup is Good. On small datasets a Nested Loop is the fastest Join.

I am assuming that item_id and room_id are not GUIDS. Otherwise that's going to need a longer answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.