Re: FULLTEXT index on two tables and many columns

[Brent Baisley]

Maybe you should rethink your whole database structure. In your 
simplified example, you really only have two pieces of data, the text 
and a qualifier (a, b, c, d, e, ...). So instead of separating your 
"text" into different columns, keep all your text in one column and add 
another column that acts as the qualifier.
You could even merge your tables since they would both end up having the 
exact same structure. You can then do self joins instead of your cross 
table joins.
Finally, this structure allows you to create unlimited qualifiers 
without having to modified the structure of your tables or indexes.

Of course, this is all based on your simplified example.

On Thursday, February 27, 2003, at 02:52 AM, Grzegorz Paszka wrote:

Hi.

I know that for fulltext index are some limitations as:
All parameters to the MATCH() function must be columns from the same 
table that
is part of the same FULLTEXT index, unless the MATCH() is IN BOOLEAN 
MODE.

But "A boolean full-text search can also work even without a FULLTEXT 
index,
although it would be SLOW."

I've such situation:

create table abc (
        a text,
        b text,
        c text
);
create table de (
        d text,
        e text
);
Size of database files is greater than amount of RAM. Twice.

I want execute query with fulltext search through columns a and b, c 
and d, a and b and c and d and e . So on.  There are 31 combinations.

First problem: creating fulltext index on columns from two tables.
Second problem: IMHO 31 fulltext indexes is too much.
Even I merge this two tables. I still must create 31 indexes...

My suggestion is:
In cases when there is need to perform search on many columns than I 
should
create 5 indexes on a,b,c,d and e. These should to be enough. Maybe 
search won't
be so fast as with 31 indexes but should be faster than search without 
31
indexes.

Regards.
--
Grzegorz
mysql,sql,query
---------------------------------------------------------------------
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 <mysql-unsubscribe-
[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
---------------------------------------------------------------------
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