UP> Hi Taka, >> I have an object model that looks like this: >> >> OBJECT 1 >> - item 1 >> - item 2 >> - item 3 >> >> OBJECT 2 >> - item 1 >> - item 2 >> >> OBJECT 3 >> - etc... >> >> These are stored in tables like this: >> >> CREATE TABLE object >> ( >> object_id INTEGER PRIMARY KEY >> ) ; >> >> CREATE TABLE item >> ( >> object_id INTEGER , >> seq_no INTEGER >> ) ; >> >> We retrieve items for a given object using this: >> >> SELECT * FROM item >> WHERE object_id = 12345 >> ORDER BY seq_no ; >> >> To speed up this up, I want to create an index on the item table. Am I >> better off creating it on the object_id column alone or both object_id >> and seq_no? What's the difference if I create a primary key on the item >> table using object_id/seq_no?
UP> If there is more than one row with the same object_id, you cannot use UP> object_id as a primary key for the item table. Primary keys need to be UP> unique for the table in question, so in your case, if you wish to use a UP> primary key, you must use both object_id AND seq_no for the item table. Yes, there may be more than one row in the item table with the same object_id. I'm asking which of these is better (i.e. faster): - no primary key but an index on object_id - no primary key but an index on object_id *and* seq_no - a primary key of object_id and seq_no but no index. - a primary key of object_id and seq_no and an index on object_id.
