On Wed, 27 Oct 2004, Taka Muraoka wrote: >UP> Hi Taka, > >>> >>> 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
Depends on how many items you'll have per object_id. If it's a small number, then you could just use object_id for index and sort the items result set. Would make inserts and updates quicker than the indexes below. >- no primary key but an index on object_id *and* seq_no >- a primary key of object_id and seq_no but no index. These two are probably similar, both in performance and implementation. One of these is probably your best bet, I'd say the first. >- a primary key of object_id and seq_no and an index on object_id. Not worth bothering. The index of (object_id,seq_no) can be used for index searches for just object_id, I believe. SQLite will also only use a single index per table when optimizing. Christian -- /"\ \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \