I am doing the following from a .cmd file to created an inverted metamorph query.
d:\morph3\tsql -d d:\morph3\texis\mktDB "DROP INDEX mkt1000000Inverted;SET ignorecase=1;set addexp='\alnum+[\/\.]{1}\alnum+';CREATE METAMORPH INVERTED INDEX mkt1000000Inverted ON mkt1000000 (Headline\ListingDescription)"
I understand using the slash to create an index on a virtual field, but what happens when I put a comma and another field?
ie
CREATE METAMORPH INVERTED INDEX mkt1000000Inverted ON mkt1000000 (Headline\ListingDescription,CategoryDescription)
Additional columns can be specified in addition to the text field to be indexed. These should be fixed length fields, such as dates, counters or numbers. The extra data in the index can be used to improve searches which combine a LIKE statement with restrictions on the other fields, or which ORDER BY some or all of the other fields.
Interesting... I have about 12-15 different fields that I index on one of my tables, because those are all the fields that the user can search by. I currently do not do any combination of fields as described above. I index each of the fields that they can search by themselves. However, they can search by any combination of these fields. Should I also create indexes with combinations of fields, in addition to each field indexed by themselves. IE
Index ListingDescription
Index DateStart
Index ListingDescription, DateStart
I currently have chkind turned off so that the metamorph queries are only updated when I tell them to be. Is there any downside to having many, many indexes other than the time it takes to create them then.
You only need one metamorph compound index with the same fields as the text field. In your example above the first index would not be needed.
You can create a metamorph compound index with all the date, integer etc fields you want, and the optimizer will pick the ones it needs from that one index.
The other downside to having too many indices is that they all need to be updated when you change a record.
So you can only create a compound index for metamorph query then?
Would the idea be then to make each metamorph query a compound one, including all of the date, integer, and double fields that can be possible searched or sorted by? And then create normal indexes on those date, integer and double fields individually? There is no down side to not having specific indexes for the metamorph fields. For example, if I have a compound index on (Headline\ListingDescription, ListingID, DateStart, DateEnd, Price), and someone does a search "where Headline\ListingDescription like 'gold'"), is it still more efficient to have the compound index, or would it be more efficient to be able to go against an index that solely worries about Headline\ListingDescription?
You can create a compound index on other fields as well, however the most common use is with the metamorph index. Once you have a metamorph index on Headline\ListingDescription there is no benefit to creating additional ones with more or fewer compound fields. In fact you may reduce performance as there would be more disk space used, and therefore the cache may not be able to cache as much useful data.
