So, Helen, if an index use the column A, then (ideally) the column A should not be updatable?
That's new for me. Greetings. Walter. On Wed, Feb 13, 2013 at 8:28 PM, Helen Borrie <[email protected]> wrote: > ** > > > At 12:44 p.m. 14/02/2013, Ruben Marti wrote: > >I have a table with several hundreds of thousands of records that has a > >composite primary key, with the fields "START_DATE" and "ITEM". > > > >I honestly do not know if it's interesting declare the index as "UNIQUE" > >when it can not have duplicate records. > > No use at all. > > > >For optimization reasons, I need an index composed of the fields "ITEM" > and > >"START_DATE". > > You should get optimal performance by creating a simple index on > START_DATE. The optimizer will use the PK index if it has to search on ITEM > alone. > > > >What will I gain or loss if the index is declared "UNIQUE"? > > No gain. Possible confusion for the optimizer. It already knows that > combination is unique. > > > >What type (UNIQUE or NOT UNIQUE) is faster to retrieve the data? > > Irrelevant. > > > >What type (UNIQUE or NOT UNIQUE) is faster to insert the data? > > Irrelevant. > > > >What type (UNIQUE or NOT UNIQUE) is faster to update the data? > > If START_DATE is an updatable field, then you have a design flaw that is > likely to hurt performance and also make your data vulnerable to invisible > corruption by users, regardless of whether the index is unique or not. > > ./hb > > Helen Borrie, Support Consultant, IBPhoenix (Pacific) > Author of "The Firebird Book" and "The Firebird Book Second Edition" > http://www.ibphoenix.com/products/books/firebird_book > __________________________________________________________ > > > [Non-text portions of this message have been removed] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
