Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
catId(INT)  |  userId(INT)  | catName(VARCHAR) 
   0               1             Cat1
   1               1             Cat2
   2               2             U2Cat1
   3               2             U2Cat2

I want to most efficiently access all the categories of a single userId (say SELECT * WHERE userId = 1)

So, imagine say, a 100 000 users, each with about 10 categories. What would be the most efficient way of modeling this.

"How to make the DB cluster this efficiently,and feel free to talk about how it would look in memory if you understand it at least on a basic level (I'm a programmer)."

Thank you.

Edit:

Here is some help to understand the question more easily:

Which of these data structures would a DB store the above table in by default, would it choose automatically between one of these in the link bellow, or do I have ways or have to force it.

http://en.wikipedia.org/wiki/Database_storage_structures

I guess I would be thankful if you happen to know of a tutorial that explains how rows and columns relate to data structures in the DB, that would help me picture it (and the correct solution, hopefully).

Thank you.

share|improve this question
    
Oh, and obviously, catID was meant as a Primary Key, but I am not sure (do not think) that a Clustered Index on catID would be the right solution here. –  Neoraptor Feb 4 '13 at 13:59
    
Why the quotes? WHere is it from? –  dezso Feb 4 '13 at 14:22
    
I quoted myself to help make it clearer to the reader, disregard. –  Neoraptor Feb 4 '13 at 15:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.