Hi all, I created a table with the following definition:

CREATE TABLE word_table (
id int(10) unsigned NOT NULL auto_increment,
word text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (id)
)

The 'word' column contains just one-word values. I want to access this
values using a dictionary for speedup reasons, and as far as I know I
should index the column, using FULLTEXT KEY (word), but this are my
concerns:
- in order to access the 'word' column values, I'll have to use
MATCH() AGAINST(), or a normal WHERE 'word' = {} should work?
- how does the fulltext work? I just have single values, and I know
that there is a 'score' provided by mySQL, does it means that if the
word is not found, I always get something or can I enforce a
"dictionary-like" behaviour?

Thanks,

Mattia

gervaz <gervaz (AT) gmail (DOT) com> wrote:

So why did you chose the type TEXT for that column? Would not
VARCHAR(20) (or alike) be a much better choice?

A FULLTEXT index is *not* a dictionary.

In order to make MySQL use the FULLTEXT index, you have to use the
MATCH syntax. But of course this makes no sense if you look for an
exact match.

It all depends on what operations you plan to do. But for exact
matches (... WHERE word = 'foobar') or head matching (... WHERE
word LIKE 'foo%') a normal BTREE index would be perfect.

Fulltext search is about proximity (opposed to exact) matching.
This makes little sense for single words. There are methods like
SOUNDEX() or n-gram matching (not in MySQL) though. The score (or
rank) is a measure how close a document is to the search term.

Especially MySQL FULLTEXT indexing is not at all useful to match
"documents" consisting of a single word only.


XL

On 21 Giu, 12:21, Axel Schwenke <axel.schwe... (AT) gmx (DOT) de> wrote:
Thank you Alex! Great explanation.

Mattia