primary key with multiple column lookup

I am new to databases. If I m开发者_如何转开发ake a primary key with two columns, userId and dayOfWeek. How can I search this primary key?

(dayOfWeek is an int where 1 = Monday, 2 = Tuesday, etc.)

Would I do use:

SELECT * 
 WHERE userId = 1 
   AND dayOfWeek = 4

Would that scan the entire database or use information provided by the primary key? Is there another more appropriate query I could use?

A primary key index is internally much like any other index, except you're explicitly saying that THIS is the key which uniquely identifies a record. It's simply an index with a "must be unique" constraint imposed on it.

Whether the DB uses the composite primary key depends on how you specify the key fields in the query. Given your PK(userID, dayOfWeek), then

SELECT * FROM mytable WHERE (userID = 1);
SELECT * FROM mytable WHERE (userID = 1) AND (dayOfWeek = 4);

would both use the primary key index, because you've used the fields in the order they're specified within the key.

However,

SELECT * FROM mytable WHERE (dayOfWeek = 4)

will not, because userID was not specified, and userID comes before dayOfWeek in the key definition.

No query will scan the entire database, unless you specified all the tables within that database. At worst, you could expect a table scan (which is what I think you really meant) if you were searching by columns that were not the primary key or indexed.

Your example is a composite primary key because it uses more than one column as the key. MySQL now automatically indexes the primary key (since v5?), so searching by all primary key columns is less likely to result in a table scan but rather an index seek/scan. It depends on if any other criteria is used. Searching by part of the primary key (IE: user_id only) might make use of the index - assuming it's a covering index, if the user_id column is the first from the left then the index could be used. Otherwise not.