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

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