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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
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.


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.

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
up vote 1 down vote accepted

If you simply want to increase the efficiency of a query, the CREATE INDEX command may interest you:

There are different index types and I believe the most common are B-Tree and hash indices. If you are interested in how the DBMS stores data then reading up on these topics may interest you.

share|improve this answer

Your Answer


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

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