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
/ \
