List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:June 21 1999 3:08pm
Subject:Re: indexes on TEXT columns, keyword searching through text
View as plain text  
Troy Grady wrote:
> 
> Folks-
> 
> 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?
> 
> Regards,
> 
> Troy
> 

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.

-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
Thread
indexes on TEXT columns, keyword searching through textTroy Grady22 Jun
  • Re: indexes on TEXT columns, keyword searching through textPaul DuBois22 Jun
  • Re: indexes on TEXT columns, keyword searching through textSasha Pachev22 Jun
    • RE: indexes on TEXT columns, keyword searching through textSteven Roussey22 Jun
      • C API webpagessimon22 Jun
        • RE: C API webpagesDouglas B. Jones22 Jun
        • Re: C API webpagesSasha Pachev22 Jun
      • Re: indexes on TEXT columns, keyword searching through textjonathan michaels23 Jun
Re: indexes on TEXT columns, keyword searching through textPhilip Hallstrom23 Jun
Re: indexes on TEXT columns, keyword searching through textTroy Grady24 Jun