Re: How does Informix evaluate the = in a join or slect

In article <34DF14DD.6E56@bloomberg.com>, Art S. Kagel <kagel@bloomberg.com> writes >David Williams wrote: >> >> In article <34DAD453.605B@cayennesoft.com>, Albert Wisse >> <wissea@cayennesoft.com> writes >> >> >I have the following question about how Informix evaluate the "=" >> >> >I have tables with a few thousand rows. >> >The key on which is selected or joined has datatype varchar[64]. >> >> >The key consist of two parts first a less unique part and then an >> >unique part, Id='<less unique part>:<unique part>' . >> >The less unique part is for about 1% unique and the unique part for >> >100%. > >> >Example: > >> >key='aaaaaaaaaaaa:1234567890' >> >key='aaaaaaaaaaaa:2345678901' > >> >I f I do a select like "select * from A where A.key = >> >'aaaaaaaaaaaa:1234567890'" how evaluate Informix the "=", start it at >> >the first word or from the last one. >> >Make a difference if the select contains a join like "select * from A, >> >B where A.key = B.key > >> >Make it sense to swap the parts in '1234567890 aaaaaaaaaaaa' > >> It should make little difference since Informix treates the whole >> columnas the index key. > >David is correct that there is no physical difference between using >the key field as you have defined it or reversing the order of the >logical sub-fields. However, searches of the index will be faster if >you either reverse the order of the sub-fields or break the key into Why does reversing the order make a difference? Would it reduce the number of B-tree levels? My mind tells me you must get the same number of index pages overall so how can it be faster? I'm having trouble visualising the different index structures.. >two distinct columns and index the unique part only, or at least first. >If you also need to search on the non-unique part index that separately >followed by the unique part (as you effectively do now). Searching >that index will be much faster than trying to search on the substring >that extracts the first sub-field in the existing index. > >My recommendation would be to break up the column to two columns and >create two indexes one unique index on only the unique column and one >on the combined key in the same order you have them now if you need >that search. If the unique part of the key is always numeric you may >want to consider making it an integer or decimal type as it will take >less storage and can be searched faster. Of course if this columns is >an intelligent value and you will need to search for substrings of it >then it must remain a char or varchar. BTW if the column is not >variable length then use char and avoid varchar. > >Art S. Kagel > >search for -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care