Troy Grady wrote:
> We store a lot of text (multi-page resumes and notes) that is too big to fit
> in VARCHAR columns and we do a lot of keyword searches through these fields
> like, for example: "find me every TEXT field that contains the word [...]
> and the word [...]" With several thousand such fields -- and growing --
> clearly we need some kind of indexing going on.
> The MySQL manual indicates that the new default table type (MYISAM) in the
> upcoming release (3.23.0) will support indexes on TEXT and BLOB columns.
> Does anyone know if this means an index on the first N characters of a TEXT
> column or an index on every word of a TEXT column?
Most of the time, an index on a TEXT field will do you no good for what
you are doing. The queries you run search of a keyword within the text.
An index can be used only if your query specifies what the field starts
with, not just what it contains.
Maybe a better solution for your setup would be to use a combination of
htdig and MySQL. You can store resumes on the filesystem, so that htdig
could index them, and all the meta information in MySQL along with a
reference to where the text is stored.