Hi.
If FULLTEXT indexes are out of question, what your suggestion is the
way to go. Although I would usually use an additional table:
CREATE TABLE places (
idp INT(8) NOT NULL,
place CHAR(32), /* Ex: "New York" */
PRIMARY KEY(idp)
);
CREATE TABLE tokens (
idt INT(8) NOT NULL AUTO_INCREMENT,
token CHAR(32), /* Ex: "New" OR "York" */
PRIMARY KEY (idt),
UNIQUE KEY (token)
);
CREATE TABLE token2places (
idt INT(8) NOT NULL,
idp INT(8) NOT NULL,
PRIMARY KEY (idt,idp)
);
The above is the standard approach for implementing a M to N
relationship. Inserts become a bit more complex, selects usually
faster. If your tables are rather small or the indexed strings consist
seldom of more than one or two words, the additional effort may not be
worthwhile.
Above I have tried to keep your design as far as possible. IMHO, you
should consider if you really need the range of INT for the different
ids or use a smaller type like MEDIUMINT instead.
Regards,
Benjamin.
On Thu, May 23, 2002 at 05:09:36PM -0300, [EMAIL PROTECTED] wrote:
> We have an application that needs to index every word (token) in a name,
> separately.
>
> Ex: "New York" must be indexed under "New" and "York".
>
> One solution is to create an auxiliary table, extract the tokens one word
> at a time, and cross-reference to the key of the original table.
>
> Ex: CREATE TABLE places
> ( idp INT(8) NOT NULL,
> place CHAR(32), /* Ex: "New York" */
> PRIMARY KEY(idp)
> )
>
> CREATE TABLE tokens
> ( idt INT(8) NOT NULL AUTO_INCREMENT,
> token CHAR(32), /* Ex: "New" OR "York" */
> idp INT(8) /* refers to same key in 'places' */
> PRIMARY KEY (idt),
> INDEX (token)
> )
> Is there a faster/more elegant way to do this?
>
> 'FULLTEXT' is not suitable.
>
> (Please forgive my naïveté, I am new to MySQL).
[...]
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
