I want to be able to have a table of records, with each record belonging to 1 or more categories. I'm thinking of creating a category field that will store which categories each record belongs to in order to keep the table flat and avoid a join. This category field would be a series of integers that represent categories. My question is would I include this category field in a metamorph index when I want to do a search across this table?
It sounds like you're going to use a varchar field to hold a list of numbers separated by space or some such. If you're going to be searching this field at the same time as your text it would generally help to include it as as auxiliary field in the metamorph index. Remember that varchar fields indexed this way will only index the declared size of the varchar field.
Another option would be to make the category field a strlst and create a regular index on it. Then you can perform a "matches" against that field.
It depends somewhat on your queries and result set sizes, but the first option that has everything it needs to know in one index would generally be fastest.
If I do make the new category field a varchar and I want to do a search on the Title\Body field and the category field, would a search on a metamorph including both of these fields search on Title\Body and category? For example, I'm searching for a record with the word 'lion' in the Title\Body and the category has a number of '12 ' or '10 '. Would the query look like this:
select * from testtable where Title\Body\category likep 'lion 12 10 '
or
select * from testtable where Title\Body likep 'lion' and category likep '12 10 '
