Millions of rows; full-text-index?

[cemk777]

Hello!

First of all, thanks in advance for your help.

We have a local database with millions of rows containing some medical terms.

It has currently such a structure:

NAME (varchar) | DESCR (TEXT)

Name field as you may guess contains name of the element etc; and description field contains texts.

Now; we want to search DESCR but search query takes so much time.

Maybe FULL-TEXT-INDEX is a solution.... But i just wanted to be sure whether any other method exists such that

NAME (varchar) | FIRST2LETTER (CHAR2)| KEYWORD (CHAR)

In this structure we insert each word of description as KEYWRD into its own row etc. Then we may have index on FIRST2LETTER and KEYWORD.

What do you advise?

Thanks again..

[steadyguy]

FULL TEXT INDEX is not a bad idea, but if you do it - be prepared to wait while the index is being generated.

Alternatively, you could generate a list of 4 or 5 keywords for each entry, then put them in an indexed keywords column ... while this would speed up your searches, it would take a lot of work for millions of records.

Another option might be to separate the texts into subjects, then use an ENUM field. This way, you could add a WHERE clause to your query, and really speed it up. Of course, this too is a lot of work.

For now, that's all I can think of, but any one of these would help quite a bit, all three would help even more.